Embedded Document DB Notes

ThinkAutomation includes an embedded server-less document database that you can use to store any arbitrary data in Json format. Data can then be queried using SQL statements. See the Embedded Data Store action. Files can also be stored and retrieved using the Embedded Files Store action. Simple key/value pair storage can also be stored using the Embedded Value Store action.

The Embedded database enables fast and easy storage & retrieval of data & files in your Automations without any additional setup.

The ThinkAutomation Embedded Document DB is similar to Mongo DB in how it operates, but requires no setup. The Embedded Database is 'schemaless' meaning the schema does not need to be pre-defined.

It uses LiteDB internally. LiteDB is a free serverless database that can be used in .NET projects enabling you to access the ThinkAutomation embedded databases from your own applications.

Limits

• Each document (record) is limited to 16mb in size.
• Any number of databases.
• Unlimited number of documents per collection.
• Up to 250 indexes per collection.
• The total size of all the collection names in a database is limited to 8000 bytes. If you plan to have many collections in a single database, make sure to use short names for your collections. For example, if collection names are 10 bytes in length, you can have 800 collections in the database.

See: https://www.litedb.org for more information.

Database Files Location

Actual database files are created when a database is first accessed. One file for each database name. Files are located in the \ProgramData\Parker Software\ThinkAutomation.NET\EmbeddedStore\ folder. Databases are global to your ThinkAutomation instance (IE: The same database can be accessed from any Automation in any of your Solutions).

Encryption

Database files can be encrypted (using AES) by specifying a password. Once a database has been created its password cannot be changed. If you need to change a password, you should create a new database and re-add the data.

Inserting Data

Data is inserted in Json format. Each 'record' is a Json Document. Each document must have an '_id' field with a unique value. If the '_id' field is not supplied then it will be added automatically with a new unique value.

For example, if two documents are added to a collection called 'Person':

{
  "BusinessEntityID": 1,
  "PersonType": "EM",
  "NameStyle": false,
  "Title": null,
  "FirstName": "Ken",
  "MiddleName": "J",
  "LastName": "Sánchez",
  "Suffix": null,
  "EmailAddress": "ken@testcompany.com"
}

{
  "BusinessEntityID": 2,
  "PersonType": "EM",
  "NameStyle": false,
  "Title": null,
  "FirstName": "Terri",
  "MiddleName": "Lee",
  "LastName": "Duffy",
  "Suffix": null,
  "EmailAddress": "terri@testcompany.com",
  "AdditionalContactInfo": null
}

A SQL select statement can then be used:

SELECT _id,EmailAddress FROM Person

Which would return:

[
  {
    "_id": "61483e2213d7e52e0023f4ee",
    "EmailAddress": "ken@testcompany.com"
  },
  {
    "_id": "61483e2213d7e52e0023f4f0",
    "EmailAddress": "terri@testcompany.com"
  }
]

When using SQL you can also return the data as CSV, which for the above would return:

61483e2213d7e52e0023f4ee,ken@testcompany.com
61483e2213d7e52e0023f4f0,terri@testcompany.com

You could also return a single document:

SELECT EmailAddress FROM Person WHERE LastName = 'Duffy'

If using the CSV return type, this would return a single value 'terri@testcompany.com'

In the above example, you could use the Ensure Indexed option on the Insert to add an index to 'LastName' to provide faster queries.

Viewing Data Using The Studio

In the Studio, select File - Embedded Data Browser, or from any of your Embedded Data Store actions within any Automations click the Browse Data button. This will open the Embedded Database Browser.

You can select any of your Databases/Collections to execute SQL SELECT statements to view your data. Click the Run button to execute. The results will be displayed. Click the Print/Export button to print or export the data locally.

SQL Statement Syntax

SELECT statement

SELECT <field> [,field]
[FROM <collectioname>]
[WHERE <filter>]
[GROUP BY <field>]
[ORDER BY <field> [,field] [ASC | DESC] ]
[LIMIT <number>]
[OFFSET <number>]

You can use aggregate functions:

COUNT(), MIN(), MAX(), FIRST(), LAST(), AVG(), SUM()

Date Functions:

YEAR(value), MONTH(value), DAY(value), HOUR(value), MINUTE(value), SECOND(value), DATEADD(dateInterval[year,month,day,hour,minute,second], amount, value), 
DATEDIFF(dateInterval, start, end), TO_LOCAL(date), TO_UTC(date)

String Functions:

LOWER(value), UPPER(value), LTRIM(value), RTRIM(value), TRIM(value), INDEXOF(value, match [,start]),  SUBSTRING(value, startIndex [,length]), LPAD(value, totalWidth, paddingChar), RPAD(value, totalWidth, paddingChar), FORMAT(value,format), LENGTH(value)

GROUP BY

If this clause is present, the results are grouped by a field and the query returns a document for each group. Please note that only one grouping field is allowed.

UPDATE statement

UPDATE <collectionname> SET <field> = <value> [,<field> = <value>]
WHERE <filter>

DELETE statement

DELETE <collectionname> 
WHERE <filter>

---