Database Tables

mos. offers a build-in SQLite based solution to persist data. Furthermore it allows you to define sources for tables that the app will automatically keep up to date. In the ./db_table directory each file represents a database table with optional static data or an optional source.

note

While mos. makes it easy to create and interact with persisent database tables basic knowledge about relational databases and SQLite are recommended to fully understand and make use of this feature.

Database Table Definition

The following example contains the definition for a persistent database table with the name users and the columns id (of type integer) and name (of type text).

{
  "table": {
    "name": "users",
    "fields": [
      {
        "name": "id",
        "type": "INTEGER"
      },
      {
        "name": "name",
        "type": "TEXT"
      }
    ],
    "primaryKey": [
      "id"
    ],
    "queries": {
      "byId": {
        "where": "id = ?"
      }
    }
  }
}

Lets go over the example and explain each field.

1. name: The name of the database table used to reference the table from layers that allow you to render your data in the UI or from other queries.
2. fields: A list of all columns the table will contain. A field consists of a name and a type.
   • name: The name the column will be created with.
   • type: The type the column will be assigned. Possible values are TEXT, INTEGER, REAL and DATETIME
3. primaryKey: A list of all columns that will be used as the primary key for the table. If empty or not defined no primary key will be used.
4. queries: See Queries

caution

By default mos. will drop and re-create a database table when it detects that its file changed leading to potential loss of data if the table is not making use of static data or a source.

Queries

In the queries map, named queries can be defined that can later be used in other parts of the config, such as getting data to display for the grid layer. You define queries by adding a new object to the queries map where the key of the object is the name of the query. When defining a new query you have two options:

1. Use pre-defined fields to filter, order and limit your result and let mos. build the actual query:

{
  "table": {
    "name": "users",
    "fields": [
      {
        "name": "name",
        "type": "TEXT"
      },
      {
        "name": "isAdmin",
        "type": "INTEGER"
      }
    ],
    "queries": {
      "admins": {
        "where": "isAdmin = 1",
        "orderBy": "name ASC",
        "limit": 5
      }
    }
  }
}

2. Use a completly custom query:

{
  "queries": {
    "admins": {
      "custom": "SELECT * FROM users WHERE isAdmin = 1 ORDER BY name ASC LIMIT 5;"
    }
  }
}

All queries may be parametrized using the typical ? syntax allowing you to pass values to the query when executing it as shown in the following example.

Definition of a query with 2 parameters.

{
  "queries": {
    "admins": {
      "custom": "SELECT * FROM users WHERE isAdmin = ? ORDER BY name ASC LIMIT ?;"
    }
  }
}

The parameters can be passed using the queryParams parameter when referencing the query from other parts of the configuration later. The parameters are bound in their respective order in the array. It is also possbible to re-use a single parameter multiple times in the same query by indexing the ? like ?1, ?2.

{
  "table": "users",
  "query": "admins",
  "queryParams": [
    "1",
    "10"
  ]
}

Custom Functions

mos. internally implements two functions that can be used inside all queries to help filter the results.

1. The REGEXP function

The REGEXPT function can be used to check a TEXT against a regular expression. The following example would check wether or not the name field contains the string "mos".

{
  "queries": {
    "mosAdmins": {
      "custom": "SELECT * FROM users WHERE REGEXPT(name, '.*mos.*');"
    }
  }
}

2. The DISTANCE function

The DISTANCE function can be used to calculate the distance between the devices current position and coordinates from your table data. In the following example we create a basic table with the name locations with latitude and longitude fields that we make use of in our withDistance query.

{
  "table": {
    "name": "locations",
    "fields": [
      {
        "name": "id",
        "type": "INTEGER"
      },
      {
        "name": "name",
        "type": "TEXT"
      },
      {
        "name": "latitude",
        "type": "REAL"
      },
      {
        "name": "longitude",
        "type": "REAL"
      }
    ],
    "primaryKey": [
      "id"
    ],
    "queries": {
      "withDistance": {
        "custom": "SELECT *, DISTANCE(latitude, longitude) AS distance FROM locations;"
      }
    }
  }
}

note

The DISTANCE function requires that the device is capable of location tracking and the user has given the corresponding permission - otherwise 0 will be returned.

Static database table data

mos. allows to easily insert static data into a database table using the data parameter. After the table creation the system will automatically insert the given data into the table. In the example below two entries will be inserted into the table.

{
  "table": {
    "name": "users",
    "fields": [
      {
        "name": "id",
        "type": "INTEGER"
      },
      {
        "name": "name",
        "type": "TEXT"
      }
    ],
    "primaryKey": [
      "id"
    ],
    "queries": {
      "byId": {
        "where": "id = ?"
      }
    }
  },
  "data": [
    {
      "id": 1,
      "name": "Jane"
    },
    {
      "id": 2,
      "name": "John"
    },
  ]
}

Database table source

mos. can automatically update a database tables content by perdiodically sending a request to a given URL. This is achieved by using the source. The source object lets you configure the request and how the table should behave when updating its content.

The following example defines a simple source for the users table that will replace its content from the given URL every hour.

{
  "table": {
    "name": "users",
    "fields": [
      {
        "name": "id",
        "type": "INTEGER"
      },
      {
        "name": "name",
        "type": "TEXT"
      }
    ],
    "primaryKey": [
      "id"
    ],
    "queries": {
      "byId": {
        "where": "id = ?"
      }
    }
  },
  "source": {
    "type": "replace",
    "request": {
      "url": "https://*",
      "method": "GET"
    },
    "dataQuery": "users",
    "update": {
      "type": "polling",
      "durations": {
        "standard": 3600
      },
      "standardDuration": "standard"
    }
  }
}

1. type: Defines if the data should be replace or updated. Possible values are insertOrUpdate and replace.
2. request: Holds information about the request that sould be made.
   • url: The URL to be used in the request.
   • method: The HTTP Method to be used by the request.
3. dataQuery: Optional string that is used to inform the app where the result that should be inserted is located in the response JSON. Necessary if the response is not a JSON Array.
4. update: Holds information about
   • type: The update type to be used. Currently the only available value is polling.
   • durations: Map of possible durations used for the interval in which tables are updated. You can switch between those durations using the setTablePollingState action.
   • standardDuration: Reference one value from the durations dictionary here that will be used as the default value.