Virtual databases are defined using the file `settings.json` and other files matching the pattern `settings*.json`. The database definitions are shared across all [[Invantive UniversalSQL]] product installations for the user.
Each database consists of one or multiple data container definitions.
Databases are grouped in "database groups" for visual and logical grouping. Databases with the same name in multiple groups are considered different databases.
<!-- BEGIN TRANSITION FROM XML - remove this block together with [[Settings.xml]] once release 28.0 has shipped. -->
> [!info] Coming from the XML format?
> Up to and including release 26.0 these definitions were held in `settings*.xml`. Release 27.0 reads `settings*.json` and migrates existing XML files automatically; release 28.0 no longer supports the XML format. See [[Settings.xml]] for the XML format, the migration and which format is used per release.
<!-- END TRANSITION FROM XML -->
The settings of the user, such as the language of the user interface and the [[Favorite Databases]], are held separately; see [[User Settings]]. Log on codes and passwords are held in [[Invantive Keychain]].
## Search Path
The default location searched for settings files varies per platform:
- On Microsoft Windows, the default folder is `%USERPROFILE%\invantive`, such as `c:\users\john.doe\invantive\settings.json`.
- On Linux and MacOS, the default folder is `$HOME/Invantive`, such as `/home/root/Invantive/settings.json`.
## Multiple Files
The folder can contain multiple `settings*.json` files, each one matching the file pattern. For instance, a file can be per customer, such as `settings-acme.json` and `settings-car.json`. All `settings*.json` valid according to the Invantive program started will automatically be merged to establish the full list of databases defined.
The actual list of available databases is limited after that step by the licence available. For instance, distributed databases are only available with an appropriate licence.
## Notation
A settings file is meant to be read and edited by a person, so it is written in a fixed layout:
- Two spaces per nesting level.
- The opening bracket, every separating comma and the closing bracket of one array or object share a single column. Adding, removing or reordering an entry therefore changes one line.
- Database groups and databases are written in ascending order of `sortingOrder`; data containers and providers in ascending order of `order`.
- Text is written as UTF-8 without a byte order mark, so that every JSON parser accepts the file.
Comments and trailing commas are tolerated when a file is read. Both disappear when an Invantive product rewrites the file.
Member names that are unknown are rejected rather than ignored, which catches a mistyped member name. For that reason a `$schema` member cannot be added to the file; associate `invantive-databases.schema.json` with the `settings*.json` file pattern in the editor instead.
## Structure
The full specification is available in `invantive-databases.schema.json` in the attachments, a JSON Schema draft 2020-12 document. The main members are:
```mermaid
classDiagram
direction TB
class settings:::classDiagram {
+$comment: string[]
+version: string
+versionUpdatedBy: string
+versionUpdatedOn: string
+versionUpdateDate: dateTime
+default: string
}
class migration:::classDiagram {
+sourceFileNamePath: string
+migratedBy: string
+migratedOn: string
+migrationDate: dateTime
+migratedUsing: string
}
class group:::classDiagram {
+name: string
+id: string
+shortDescription: string
+description: string
+comment: string
+sortingOrder: int
+iconResourceName16: string
+iconResourceName32: string
+createdBy: string
+createdOn: string
+creationDate: dateTime
}
class database:::classDiagram {
+name: string
+id: string
+shortDescription: string
+description: string
+comment: string
+sortingOrder: int
+authentication: AuthenticationModeType
+sqlTrace: boolean
+starred: boolean
+manual: boolean = false
+forceNative: boolean = false
+iconResourceName16: string
+iuidSource: string
+dataCacheConnectionString: string
+dataDictionaryConnectionString: string
+createdBy: string
+createdOn: string
+creationDate: dateTime
+lastUsedDate: dateTime
}
class dataContainer:::classDiagram {
+kind: string = dataContainer
+order: int
+alias: string
+provider: string
+connectionString: string
+allowConnectionStringRewrite: boolean = true
+allowConnectionPooling: boolean = true
+description: string
+comment: string
+defaultUserLogonCode: string
+userLogonCodeMode: EditabilityType
+defaultPassword: string
+passwordLabel: string
+passwordHint: string
+userLogonCodeLabel: string
+userLogonCodeHint: string
+passwordMode: EditabilityType
+createdBy: string
+createdOn: string
+creationDate: dateTime
}
class credentials:::classDiagram {
+kind: string = credentials
+order: int
+databaseSegment: string
+server: string
+dataContainers: string
+description: string
+comment: string
+defaultUserLogonCode: string
+userLogonCodeMode: EditabilityType
+defaultPassword: string
+passwordLabel: string
+passwordHint: string
+userLogonCodeLabel: string
+userLogonCodeHint: string
+passwordMode: EditabilityType
+createdBy: string
+createdOn: string
+creationDate: dateTime
}
class provider:::classDiagram {
+order: int
+file: string
+class: string
+comment: string
+createdBy: string
+createdOn: string
+creationDate: dateTime
}
class attribute:::classDiagram {
+code: string
+value: string
}
settings "1" *-- "0..1" migration : migration
settings "1" *-- "0..*" group : groups
group "1" *-- "0..*" database : databases
database "1" *-- "0..*" provider : providers
provider "1" *-- "0..*" attribute : attributes
database "1" *-- "0..*" dataContainer : dataContainers
database "1" *-- "0..*" credentials : dataContainers
```
A database consists of one or more data containers. All of them are held in the `dataContainers` array of the database, where the member `kind` states which of the two kinds an entry is:
- `dataContainer`: one for each locally defined data container;
- `credentials`: one for one, multiple or all data containers in an [[Invantive Cloud/Invantive Cloud|Invantive Cloud]] managed database (via [[Invantive Credentials Server]]).
Take care not to confuse the two members named `dataContainers`: on a database it is the array of data containers, whereas on a `credentials` entry it is a comma-separated list of data container aliases to include, with `*` denoting all of them.
With `EditabilityType` being one of:
- `Auto`,
- `ReadOnly`,
- `Hidden`.
With `AuthenticationModeType` one of:
- `Default`,
- `Database`,
- `Application`.
The following members are accepted but obsolete, and are left out of the structure above:
- `failovers` on a database;
- `encryptedDataCacheConnectionString` and `encryptedDataDictionaryConnectionString` on a database;
- `forceDefault` on the settings file itself.
The encrypted connection string of a data container has no counterpart in JSON: it is written and read in the XML format only.
## Examples
The following database definition provides a PostgreSQL database, the cloud APIs of the Dutch parliament as well as the Exact Online and Salesforce data containers of an Invantive Cloud managed database:
```json
{ "version": "5"
, "groups":
[ { "name": "ACME"
, "sortingOrder": 0
, "shortDescription": "ACME"
, "databases":
[ { "name": "ACME"
, "sortingOrder": 10
, "authentication": "Database"
, "dataContainers":
[ { "kind": "dataContainer"
, "order": 10
, "alias": "db"
, "provider": "PostgreSql"
, "userLogonCodeMode": "Hidden"
, "passwordMode": "Hidden"
, "connectionString": "Server=host.acme.eu;Database=xxacme;User Id=xxacme_user;Password=secret;CommandTimeout=3600"
, "allowConnectionStringRewrite": false
}
, { "kind": "dataContainer"
, "order": 20
, "alias": "twk"
, "provider": "TweedeKamerNl"
, "allowConnectionPooling": false
}
, { "kind": "credentials"
, "order": 30
, "description": "Exact Online and Salesforce database"
, "databaseSegment": "acme-exact-online-salesforce"
, "dataContainers": "*"
, "userLogonCodeMode": "Hidden"
, "passwordMode": "Hidden"
, "defaultUserLogonCode": "
[email protected]"
, "defaultPassword": "secret"
}
]
}
]
}
]
}
```