Database APIs

Created: - Updated:

This article covers these topics:

Introduction to survey database APIs

Lumi Say survey database APIs can be used to automate the upload and update of  a database file to be used in the survey. This file can be marked as online or offline depending on how you want to use it within the survey.

  • Local survey database: if you are expecting your respondents to have bad to no connection, the database can be queried offline.  The database will be downloaded to the device.
  • Remote survey database: if the database file is too large (>250MB) and your respondents are aware the survey is an online task, the database can only be queried online.

Upload local survey database API

This is a web service for uploading a database file in csv format into the server via an HTTP POST that must be done over HTTPS. Essentially the service can be used to automate uploading database files into your Lumi Say server from any backend.   This database file is saved as part of the survey resources and is downloaded in the app in order to work offline.

POSTS are directed to the Lumi Say server of your own project using a URL with the following format

https://<domain>/reactor-webapp/pt/<project>/pn/lsdbcache/api/upload_database

POST Parameters

Name

Description

Value

csv_file

The database file in csv format

 

user_name

A back-end user with the right permissions and company access

Your dashboard username

password

A correct password for the user, so we can authenticate

Your Dashboard password

survey_id

The id of the survey this database should be uploaded to.

Project id

is_online

This determines if the file should be used online or offline.

0

Return values

HTTP error responses

Code

Message

Reason

HTTP 400

Action not supported

Not a POST request

HTTP 403

Not supported

Not using HTTPS

A valid request (POST request over HTTPS) in the correct format will have a status that is either OK for successful request or error if the request has failed, the status is accompanied with a descriptive message that explains what went wrong according to the table below

Status

Message

Reason

error

Authentication failure.

Failed to authenticate with given credentials.

error

Could not convert or upload csv file.

General exception caught message.

ok

Database successfully uploaded and created.

success message

error

Failed to create database. Could not create database from csv due to SQL Exception.

Creating db file failed due to SQLException.

error

Failed to create database. Could not create database from csv.

Creating db file failed due to parse exception.

error

Failed to create database. Could not create db file.

The created db file is empty.

error

Failed to create database. Could not create temporary db file.

Creating db file failed due to IOException.

error

Failed to create database. Could not store csv as resource file.

Failed to save db file as a compass resource.

error

Failed to create database. Could not store database in survey xml.

Failed to insert the db metadata in the survey xml.

error

Failed to create database. Failure: Survey not found.

Survey does not exist or user does not have access to it.

error

Failed to create database. File not found.

Filename not found or size 0.

error

Failed to create database. Missing survey id parameter.

Could not parse survey_id parameter from request.

error

Failed to create database. Uploaded file is not in csv format.

Not a .csv file.

error

Missing credential parameters.

Username or password missing.

error

Request is not multipart.

Request is not multipart.

Upload remote survey database API

This works in the same way as Local survey database API.  To specify the upload as remote, make sure to set the is_online parameter to 1 in the call.

Update survey database API

This is a web service for updating an existent database file in the server via an HTTP POST that must be done over HTTPS. Essentially the service can be used to automate syncing of the database files between Lumi Say backend and any other backend.  This database file is used either online or offline depending on the parameter ‘is_online’ value that is specified in the upload call.

POSTS are directed to the Lumi Say server of your own project using a URL with the following format

https://<branded_instance>/reactor-webapp/pt/<project_name>/pn/lsdbcache/api/update_database

POST Parameters

Name

Description

Value

user_name

A back-end user with the right permissions and company access

Your dashboard username

password

A correct password for the user, so we can authenticate

Your Dashboard password

data

The new data to be appended to the existent database file

JSON string in the format specified below

JSON Format

{
"survey_id": "",
"database_name": "",
"overwrite": "",
"db_rows": [
{
"id": "1",
"column1": "",
"column2": ""
}
]
}

JSON string parameters

Parameter

Description

Value

Required

survey_id

The id of the survey this database should be uploaded to.

Project id

Yes

database_name

The name of the database file

csv file name, this is case sensitive

Yes

overwrite

This determines whether you want to update (overwrite) an existing row in the database file or append a new one

true if you want to overwrite/false if not.

No. It defaults to true when not specified

db_rows

Contains the row data

JSON array of the row data

Yes

id

The id of the column being updated/appended

Column id

Yes

columns

The rest of the columns to be added/updated

string

No

Return values

HTTP error responses

Code

Message

Reason

HTTP 400

Action not supported

Not a POST request

HTTP 403

Not supported

Not using HTTPS

A valid request (POST request over HTTPS) in the correct format will have a status that is either OK for successful request or error if the request has failed, the status is accompanied with a descriptive message that explains what went wrong according to the table below

Status

Message

Reason

ok

Database successfully updated!

success message

error

Missing credential parameters.

Username or password missing.

error

Authentication failure.

Failed to authenticate with given credentials.

error

Could not find database metadata in survey!

Could not get the <database> xml element from the survey xml. Likely something went wrong when uploading the database in the first place.

error

Could not update row, missing required id column value.

One or more rows in the db_rows json does not include a value for the id column.

error

Could not open database file for insert.

Could not find the sqlite database file on the server.

error

Could not parse json parameter as a JSONObject.

Error when parsing JSON - either because a required parameter was missing or there was a syntax error. The exception message is appended to make it easier to find the issue.

error

Survey not found!

Could not find a survey with the indicated survey_id from storage.

error

Could not open database!

The database could not be opened because of an exception.

error

Could not update database!

General exception occured - includes the exception message at the end.