Module: sqlite-manager

Module to manage the sqlite database.

nqminds-blue-logo.png interlinq-logo-darker.png

Author:
Source:

Methods

(async, static) addData(db, data) → {Promise.<object.<string, int>>}

Add data to a dataset resource.

Parameters:
Name Type Description
db object

The sqlite3 db object from module node-sqlite3.
data DataRow | Array.<DataRow>

The data to add. Must conform to the schema defined by the resource metadata. Supports creating an individual document or many documents.
Source:
Returns:
  • The promise with the total count of rows added.
Type
Promise.<object.<string, int>>
Examples

create an individual document

// returns {"count": 1} if successful
manager.addData(db, {lsoa: "E0000001", count: 398});

create multiple documents

manager.addData(db, [
 {lsoa: "E0000001", count: 398},
 {lsoa: "E0000002", count: 1775},
 {lsoa: "E0000005", count: 4533},
]);

add a 2D ndarray

buffer = Buffer.alloc(23*34);
manager.addData(db, {id: 1, array: manager.getNdarrayMeta(buffer, "float64", [23, 34])});

(static) closeDatabase(db) → {Promise.<object>}

Closes a sqlite database.

Parameters:
Name Type Description
db object

The sqlite3 db object from module node-sqlite3
Source:
Returns:
  • The empty promise or error
Type
Promise.<object>

(static) createDataset(db, options) → {Promise.<string>}

Creates a dataset in the sqlite database.

Parameters:
Name Type Description
db object

The sqlite3 db object from module node-sqlite3
options object

details of the dataset to be added

Properties
Name Type Attributes Default Description
basedOnSchema string <optional>
 "dataset"

the id of the schema on which this resource will be based.
derived object <optional>

definition of derived filter, implying this resource is a view on an existing dataset.

Properties
Name Type Attributes Description
filter object <optional>

the (read) filter to apply, in mongodb query format, e.g. {"temperature": {"$gt": 15}} will mean that only data with a temperature value greater than 15 will be available in this view. The filter can be any arbitrarily complex mongodb query. Use the placeholder "@@_identity_@@" to indicate that the identity of the currently authenticated user should be substituted. For example, if the user `bob@acme.com/tdx.acme.comis currently authenticated, a filter of{"username": "@@identity@@"}will resolve at runtime to{"username": "bob@acme.com/tdx.acme.com"}`.
projection object <optional>

the (read) projection to apply, in mongodb projection format, e.g. {"timestamp": 1, "temperature": 1} implies only the 'timestamp' and 'temperature' properties will be returned.
source string <optional>

the id of the source dataset on which to apply the filters and projections.
writeFilter object <optional>

the write filter to apply, in mongodb query format. This controls what data can be written to the underlying source dataset. For example, a write filter of {"temperature": {"$lt": 40}} means that attempts to write a temperature value greater than or equal to 40 will fail. The filter can be any arbitrarily complex mongodb query.
writeProjection object <optional>

the write projection to apply, in mongodb projection format. This controls what properties can be written to the underlying dataset. For example, a write projection of {"temperature": 1} means that only the temperature field can be written, and attempts to write data to other properties will fail. To allow a view to create new data in the underlying dataset, the primary key fields must be included in the write projection.
description string <optional>

a description for the resource.
id string <optional>

the requested ID of the new resource. Must be unique. Will be auto-generated if omitted (recommended).
name string <optional>

the name of the resource. Must be unique in the parent folder.
meta object <optional>

a free-form object for storing metadata associated with this resource.
parentId string <optional>

the id of the parent resource. If omitted, will default to the appropriate root folder based on the type of resource being created.
provenance string <optional>

a description of the provenance of the resource. Markdown format is supported.
schema object <optional>

optional schema definition.

Properties
Name Type Attributes Description
dataSchema object <optional>

data schema definition object. Has TDX object structure.
uniqueIndex Array.<object> <optional>

array of key value pairs denoting the ascending or descending order of the columns.
shareMode string <optional>

the share mode assigned to the new resource. One of ["pw", "pr", "tr"], corresponding to: "public read/write", "public read/trusted write", "trusted only".
tags Array.<string> <optional>

a list of tags to associate with the resource.
Source:
Returns:
  • The id of the dataset created
Type
Promise.<string>
Example

create a dataset with give id and schema

 manager.createDataset(db, {
   "id": "12345",
   "schema": {
     "dataSchema": {
       "prop1": {"__tdxType": ["number"]}
     },
     "uniqueIndex": [{"asc": "prop1"}]
   }
 });

(async, static) deleteData(db, data, doNotThrowopt)

Deletes data from a dataset-based resource.

Parameters:
Name Type Attributes Default Description
db object

The sqlite3 db object from module node-sqlite3.
data DataRow | Array.<DataRow>

The primary key data to delete.
doNotThrow boolean <optional>
 false

set to override default error handling.
Source:

(static) getData(db, filteropt, projectionopt, optionsopt) → {Promise.<DatasetData>}

Gets all data from the given dataset that matches the filter provided.

Parameters:
Name Type Attributes Description
db object

The sqlite3 db object from module node-sqlite3.
filter object <optional>

A mongodb filter object. If omitted, all data will be retrieved.
projection object <optional>

A mongodb projection object. Should be used to restrict the payload to the minimum properties needed if a lot of data is being retrieved.
options object <optional>

A mongodb options object. Can be used to limit, skip, sort etc. Note a default limit of 1000 is applied if none is given here.

Properties
Name Type Attributes Description
skip number <optional>

Number of documents to skip.
limit number <optional>

Limit number of documents to output.
sort Object <optional>

Sorting object by schema keys: e.g. {prop1: 1, prop2: -1}, where 1 = ascending, -1 = descending.
nqmMeta boolean <optional>

When set, the resource metadata will be returned along with the dataset data. Can be used to avoid a second call to getResource. Otherwise a URL to the metadata is provided.
Source:
Returns:
Type
Promise.<DatasetData>

(static) getDatasetData(db, filteropt, projectionopt, optionsopt) → {Promise.<DatasetData>}

Parameters:
Name Type Attributes Description
db object

The id of the dataset-based resource.
filter object <optional>

A mongodb filter object. If omitted, all data will be retrieved.
projection object <optional>

A mongodb projection object. Should be used to restrict the payload to the minimum properties needed if a lot of data is being retrieved.
options object <optional>

A mongodb options object. Can be used to limit, skip, sort etc. Note a default limit of 1000 is applied if none is given here.

Properties
Name Type Attributes Description
nqmMeta boolean <optional>

If:

  • true, the resource metadata will be returned along with the dataset data. Can be used to avoid a second call to getResource.
  • false-y, a URL to the metadata is provided.
Deprecated:
  • use getData() Gets all data from the given dataset that matches the filter provided.
Source:
Returns:
Type
Promise.<DatasetData>

(static) getDatasetDataCount(db, filter) → {object}

Gets a count of the data in a dataset-based resource, after applying the given filter.

Parameters:
Name Type Description
db object

The sqlite3 db object from module node-sqlite3.
filter object

An optional mongodb filter to apply before counting the data.
Source:
Returns:
  • The promise with the total count of rows.
Type
object

(static) getDistinct(db, key, filteropt) → {Promise.<Array.<object>>}

Gets a list of distinct values for a given property in a dataset-based resource.

Parameters:
Name Type Attributes Description
db object

The sqlite3 db object from module node-sqlite3.
key string

The name of the property to use. Can be a property path, e.g. "address.postcode".
filter object <optional>

An optional mongodb filter to apply.
Source:
Returns:
Type
Promise.<Array.<object>>

(static) getGeneralSchema(db) → {object}

Returns the general schema.

Parameters:
Name Type Description
db object

The sqlite3 db object from module node-sqlite3.
Source:
Returns:
  • The general schema object
Type
object

(async, static) getResource(db, noThrowopt) → {Promise.<Resource>}

Gets the details for a given database.

Parameters:
Name Type Attributes Default Description
db object

The sqlite3 db object from module node-sqlite3.
noThrow boolean <optional>
 false

If set, the call won't reject or throw if the resource doesn't exist.
Source:
Throws:

Will throw/reject if the resource is not found (see noThrow flag) or permission is denied.

Returns:
Type
Promise.<Resource>

(async, static) openDatabase(path, type, mode) → {Promise.<object>}

Opens a sqlite database. Creates if none exists.

Parameters:
Name Type Description
path string

The path of the db
type string

The type of the db: "file" or "memory"
mode string

The open mode of the db: "w+" or "rw" or "r"
Source:
Returns:

Returns the sqlite3 db object from module node-sqlite3

Type
Promise.<object>

(static) setGeneralSchema(db, schema)

Sets the general schema and the default NULL array.

Parameters:
Name Type Description
db object

The sqlite3 db object from module node-sqlite3.
schema object

The general schema.
Source:

(static) truncateResource(db) → {object}

Truncates the dataset resource.

Parameters:
Name Type Description
db object

The sqlite3 db object from module node-sqlite3.
Source:
Returns:
  • The promise with the total count of rows deleted.
Type
object

(async, static) updateData(db, data, upsertopt, throwsopt) → {Promise.<CommandResult>}

Updates data in a dataset resource.

Parameters:
Name Type Attributes Default Description
db object

The sqlite3 db object from module node-sqlite3.
data DataRow | Array.<DataRow>

The data to update. Must conform to the schema defined by the resource metadata. Supports updating individual or multiple rows.
upsert boolean <optional>
 false

Indicates the data should be created if no document/row is found matching the primary key.
throws boolean <optional>
 true

Indicates whether this function should reject if there is an error. The TDX-API doesn't, as it returns a field which states if there has been an error.
Source:
Returns:
  • Use the result property to check for errors.
Type
Promise.<CommandResult>

(static) updateDataByQuery(db, query, update) → {Promise.<object>}

Updates data in a dataset-based resource using a query to specify the documents to be updated.

Parameters:
Name Type Description
db object

The sqlite3 db object from module node-sqlite3.
query object

The query that specifies the data to update. All documents matching the query will be updated.
update object

The update object with field data to be replaced.
Source:
Returns:

The promise with the total count of rows updated.

Type
Promise.<object>
Example

updates multiple documents

// Update all documents with English lsoa, setting `count` to 1000.
manager.updateDataByQuery(db, {lsoa: {$regex: "E*"}}, {count: 1000});