Applicable Product:
- Learning
Applicable Release:
- All version
API support in the LMS provides a mechanism to integrate the LMS with external systems, and particularly to support portal front-end interaction and data warehouse queries. As these requirements are constantly evolving there is a need to provide a more responsive framework for making new APIs available, hence the rationale for dynamic APIs.
The dynamic API approach provides an almost immediate way in which new SQL queries can be defined. A new API is associated with a pre-defined API label in a properties file to enable new API interactions without a customization, or a new release product development exercise being required, essentially speeding up the addition of simple data retrieval based APIs.
Defining a New API
A new properties file, api.properties, which is located in the .../WEB-INF/conf folder, enables new queries to be defined. A typical file with three API entries would appear as follows:################################################################################
# Dynamic API Properties File# $Revision: 1.000 $#################################################################################---------------------------------------------------------------# APINAME: All SQL strings have a key/name of the form "api.<name>"## PARAMETERS: Individual parameters are denoted in sequential order by P<n>. It is# assumed that the user will format the SQL to match the format of the parameter as# used in the API call (e.g. include enclosing '' as makes sense for string values). Direct text substitution is# done in processing the statement, where all text is treated as a string.### RESULTS: Returned results are listed as comma separated fields as defined in the SQL# statement itself - no additional formatting of individual columns is done.## Example SQL:#api.course_not_started=select count(*) from transcript where learningid = ''{p1}'' and enrollmentDate > ''{p2}'' and studentstatus = 2#api.suspended_users=select familyname, givenname, email from users where currentstatus = 1api.new_users=select familyname, givenname, email from users where datecreated > ''{p1}''The primary reason for putting these statements into a properties file instead of allowing direct browser entry is to ensure that all dynamic APIs added to the system are reviewed prior to production deployment (to manage security risks), and to enable SQL upgrades to be performed by Professional Services and/or the client by reviewing the file and changing it as required prior to the LMS upgrade.
There are no restrictions on the SQL statement as entered into the api.properties file, meaning that it can include complex T-SQL related to JOINS and DB FUNCTIONS, and can include zero or more parameters as needed. However, once new entries are made, the LMS needs to be restarted for these to take effect.
Invoking the API
Examples of invocations of the above statements are:
dynamicapi?apiname=courses_not_started&p1=abc101&p2=2021-01-01dynamicapi?apiname=suspended_usersdynamicapi?apiname=new_users&p1=2021-01-01 |
An incorrect apiname will return a Bad Request - 400 HTTP error. An error in the SQL execution will return an Internal Server Error - 500 response.
Authentication
All APIs use system authentication (for example, the authentication.key value in ekp.properties).
Output
Current output is returned in CSV tabular format without headers. JSON and XML output are not supported by the LMS. For example:
"James","Taylor","james.mcdonald@peoplefluent.com""John","Smith","j.smith@peoplefluent.com""Jane","Doe","jane.doe@anywhere.com" |
Any special result set formatting (for example, dates) requirement needs to be handled in the SQL.