Databases

io.Manager supports connecting to Microsoft SQL Server databases. The following sections outline the steps and requirements for configuring io.Manager to work with Microsoft SQL Server databases and the options for initializing or migrating the database schema.

To configure io.Manager to connect to a Microsoft SQL Server database, you must either set the necessary environment variables, or provide the required configuration at runtime.

Depending on the deployment approach you have chosen, you have the following options:

• If you are using the basic deployment scenario from the template repository approach, you must set properly the necessary environment variables.

• If you are using the NPM packages for deployment, or the advanced deployment scenario from the template repository approach, you must provide the necessary configuration settings at runtime when initializing the io.Manager Server.

The following sections provide examples of both options.

Environment Variables

To configure io.Manager to connect to a Microsoft SQL Server database, you must register all of the following environment variables with the proper values. The API_STORE_TYPE environment variable must be set to mssql. All other variables must be set with values according to your specific environment:

Example settings:

API_STORE_TYPE=mssql
API_STORE_MSSQL_SERVER=localhost
API_STORE_MSSQL_PORT=1433
API_STORE_MSSQL_DB_NAME=my_db
API_STORE_MSSQL_CREATE_DB=true
API_STORE_MSSQL_DOMAIN=MYDOMAIN
API_STORE_MSSQL_USERNAME=my_user
API_STORE_MSSQL_PASSWORD=password

Runtime Configuration

To configure io.Manager to connect to a Microsoft SQL Server database, you must provide the necessary settings at runtime when initializing the io.Manager Server. Use the store property of the optional Config object and provide an MSSQLStoreConfig object as its value.

The following example demonstrates configuring the connection to a Microsoft SQL Server database when initializing the io.Manager Server:

import { start } from "@interopio/manager";

// Configuration for the io.Manager Server.
const config = {
    name: "my-server",
    port: 4242,
    token: {
        secret: "my-secret"
    },
    // Configuration for connecting to a Microsoft SQL Server database.
    store: {
        type: "mssql",
        server: "localhost",
        port: 1433,
        dbName: "my_db",
        userName: "my_user",
        password: "password"
    }
};

// Initializing the io.Manager Server.
const server = await start(config);

The store object has the following properties:

The io.Manager schema for Microsoft SQL Server databases can be created or migrated in two ways: automatically, when starting the io.Manager Server, or by using a schema creation script.

Automatic Creation & Migration

io.Manager provides an automated functionality for creating or migrating the database schema. This functionality uses Knex.js migrations internally. The creation or migration of the database schema is executed on startup of the server, only if required.

To enable io.Manager to use this automated functionality, the user configured in io.Manager for connecting to the database must meet at least one of the following requirements:

• the user has the CREATE DATABASE permission in the master database;
• the user has the CREATE ANY DATABASE permission;
• the user has the ALTER ANY DATABASE permission;

Schema Creation Script

If you don't want to use the automated functionality of io.Manager for creating and migrating the database schema, you can use the SQL script provided in this section. Execute the script before initializing the io.Manager Server in order to create the necessary database and tables.

⚠️ Note that when you create the database yourself, you must create it with collation as in the following example:

CREATE DATABASE my_db COLLATE SQL_Latin1_General_CP1_CS_AS

Prerequisites

Using this script means that you must disable the automated functionality of io.Manager for creating or migrating the database schema.

If you are using the basic deployment scenario from the template repository approach, you must set the API_STORE_MSSQL_CREATE_DB environment variable to false:

API_STORE_MSSQL_CREATE_DB=false

If you are using the NPM packages for deployment, or the advanced deployment scenario from the template repository approach, you must set the createDatabaseAndTables property to false in the optional Config object for initializing the io.Manager Server:

import { start } from "@interopio/manager";

const config = {
    name: "my-server",
    port: 4242,
    token: {
        secret: "my-secret"
    },
    store: {
        type: "mssql",
        server: "localhost",
        port: 1433,
        dbName: "my_db",
        userName: "my_user",
        password: "password",
        // Disable the automated functionality for creating the database.
        createDatabaseAndTables: false
    }
};

const server = await start(config);

SQL Script for Creating the Database

⚠️ Note that my_user in the script must already be created. Replace my_user with the actual user name where necessary.

-- io.Manager schema initialization script for Microsoft SQL Server databases.

IF NOT EXISTS (SELECT * FROM sysobjects WHERE name='groups' and xtype='U')
CREATE TABLE groups
(
    groups_id INT IDENTITY PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    CONSTRAINT groups_name_unique UNIQUE (name)
);

IF NOT EXISTS (SELECT * FROM sysobjects WHERE name='last_updated' and xtype='U')
CREATE TABLE last_updated
(
    last_updated_id INT IDENTITY PRIMARY KEY,
    applications BIGINT NOT NULL,
    layouts BIGINT NOT NULL,
    groups BIGINT NOT NULL,
    commands BIGINT NOT NULL,
    configs BIGINT NOT NULL
);

IF NOT EXISTS (SELECT * FROM sysobjects WHERE name='layouts' and xtype='U')
CREATE TABLE layouts
(
    layouts_id INT IDENTITY PRIMARY KEY,
    id VARCHAR(255) NOT NULL,
    type VARCHAR(255) NOT NULL,
    name VARCHAR(255) NOT NULL,
    owner VARCHAR(255) NULL,
    "isPublic" BIT NOT NULL,
    disabled BIT NOT NULL,
    "accessList" NVARCHAR(max) NULL,
    definition TEXT NOT NULL,
    "createdBy" VARCHAR(255) NOT NULL,
    "createdOn" BIGINT NOT NULL,
    "lastModifiedBy" VARCHAR(255) NULL,
    "lastModifiedOn" BIGINT NULL,
    "isDefault" BIT NULL,
    CONSTRAINT layouts_id_unique UNIQUE (id)
);

IF NOT EXISTS (SELECT * FROM sysobjects WHERE name='machines' and xtype='U')
CREATE TABLE machines
(
    machines_id INT IDENTITY PRIMARY KEY,
    id VARCHAR(255) NOT NULL,
    "user" VARCHAR(255) NOT NULL,
    os NVARCHAR(max) NULL,
    name VARCHAR(255) NULL,
    displays NVARCHAR(max) NULL,
    CONSTRAINT machines_id_unique UNIQUE (id)
);

IF NOT EXISTS (SELECT * FROM sysobjects WHERE name='prefs' and xtype='U')
CREATE TABLE prefs
(
    prefs_id INT IDENTITY PRIMARY KEY,
    id VARCHAR(255) NOT NULL,
    app VARCHAR(255) NOT NULL,
    "user" VARCHAR(255) NOT NULL,
    data NVARCHAR(max) NOT NULL,
    "lastUpdate" BIGINT NOT NULL,
    CONSTRAINT prefs_id_unique UNIQUE (id)
);

IF NOT EXISTS (SELECT * FROM sysobjects WHERE name='sessions' and xtype='U')
CREATE TABLE sessions
(
    sessions_id INT IDENTITY PRIMARY KEY,
    id VARCHAR(255) NOT NULL,
    machine VARCHAR(255) NOT NULL,
    start BIGINT NOT NULL,
    "user" VARCHAR(255) NOT NULL,
    glue NVARCHAR(max) NOT NULL,
    "lastDataFetch" BIGINT NULL,
    "end" BIGINT NULL,
    closed BIT NULL,
    "closeReason" VARCHAR(255) NULL,
    CONSTRAINT sessions_id_unique UNIQUE (id)
);

IF NOT EXISTS (SELECT * FROM sysobjects WHERE name='users' and xtype='U')
CREATE TABLE users
(
    users_id INT IDENTITY PRIMARY KEY,
    id VARCHAR(255) NOT NULL,
    email VARCHAR(255) NULL,
    password VARCHAR(255) NULL,
    apps NVARCHAR(max) NOT NULL,
    groups NVARCHAR(max) NOT NULL,
    "lastUpdated" BIGINT NULL,
    "firstName" VARCHAR(255) NULL,
    "lastName" VARCHAR(255) NULL,
    CONSTRAINT users_id_unique UNIQUE (id)
);

IF NOT EXISTS (SELECT * FROM sysobjects WHERE name='"glue42SystemConfig"' and xtype='U')
CREATE TABLE "glue42SystemConfig"
(
    "glue42SystemConfig_id" INT IDENTITY PRIMARY KEY,
    identifier NVARCHAR(450) NOT NULL UNIQUE,
    config NVARCHAR(max) NOT NULL
    CONSTRAINT "glue42SystemConfig_identifier_unique" UNIQUE (identifier)
)

IF NOT EXISTS (SELECT * FROM sysobjects WHERE name='feedback' and xtype='U')
CREATE TABLE feedback
(
    feedback_id INT IDENTITY PRIMARY KEY,
    id VARCHAR(255) NOT NULL,
    date BIGINT NOT NULL,
    "user" VARCHAR(255) NOT NULL,
    session VARCHAR(255) NOT NULL,
    description TEXT NOT NULL,
    attachment TEXT NOT NULL,
    reviewed BIT NULL,
    comment TEXT NULL,
    CONSTRAINT feedback_id_unique UNIQUE (id)
);

IF NOT EXISTS (SELECT * FROM sysobjects WHERE name='crashes' and xtype='U')
CREATE TABLE crashes
(
    crashes_id INT IDENTITY PRIMARY KEY,
    id VARCHAR(255) NOT NULL,
    date BIGINT NOT NULL,
    "user" VARCHAR(255) NULL,
    info NVARCHAR(max) NOT NULL,
    reviewed BIT NULL,
    comment TEXT NULL,
    CONSTRAINT crashes_id_unique UNIQUE (id)
);

IF NOT EXISTS (SELECT * FROM sysobjects WHERE name='commands_for_version' and xtype='U')
CREATE TABLE commands_for_version
(
    commands_for_version_id INT IDENTITY PRIMARY KEY,
    version VARCHAR(255) NOT NULL,
    commands NVARCHAR(max) NOT NULL,
    CONSTRAINT commands_for_version_version_unique UNIQUE (version)
);

IF NOT EXISTS (SELECT * FROM sysobjects WHERE name='commands' and xtype='U')
CREATE TABLE commands
(
    commands_id INT IDENTITY PRIMARY KEY,
    id VARCHAR(255) NOT NULL,
    "user" VARCHAR(255) NOT NULL,
    status VARCHAR(255) NOT NULL,
    session VARCHAR(255) NOT NULL,
    machine VARCHAR(255) NOT NULL,
    "createdBy" VARCHAR(255) NOT NULL,
    "createdAt" BIGINT NOT NULL,
    command VARCHAR(255) NOT NULL,
    "commandParams" VARCHAR(255) NULL,
    "resultData" NVARCHAR(max) NULL,
    "resultAt" BIGINT NULL,
    "resultType" VARCHAR(255) NULL,
    CONSTRAINT commands_id_unique UNIQUE (id)
);

IF NOT EXISTS (SELECT * FROM sysobjects WHERE name='audit' and xtype='U')
CREATE TABLE audit
(
    audit_id INT IDENTITY PRIMARY KEY,
    id VARCHAR(255) NOT NULL,
    "user" VARCHAR(255) NULL,
    parent VARCHAR(255) NULL,
    date BIGINT NOT NULL,
    session VARCHAR(255) NULL,
    server VARCHAR(255) NULL,
    "entityType" VARCHAR(255) NOT NULL,
    "entityId" VARCHAR(255) NULL,
    "entityDisplayName" VARCHAR(255) NULL,
    operation VARCHAR(255) NOT NULL,
    "operationComment" VARCHAR(255) NULL,
    "newValue" NVARCHAR(max) NULL,
    "oldValue" NVARCHAR(max) NULL,
    request NVARCHAR(max) NULL,
    response NVARCHAR(max) NULL,
    CONSTRAINT audit_id_unique UNIQUE (id)
);

IF NOT EXISTS (SELECT * FROM sysobjects WHERE name='app' and xtype='U')
CREATE TABLE app
(
    app_id INT IDENTITY PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    disabled BIT NOT NULL,
    definition NVARCHAR(max) NOT NULL,
    "isPublic" BIT NOT NULL,
    "accessList" NVARCHAR(max) NULL,
    "createdBy" VARCHAR(255) NOT NULL,
    "createdOn" BIGINT NOT NULL,
    "lastModifiedBy" VARCHAR(255) NULL,
    "lastModifiedOn" BIGINT NULL,
    CONSTRAINT app_name_unique UNIQUE (name)
);

IF NOT EXISTS (SELECT * FROM sysobjects WHERE name='blobs' and xtype='U')
CREATE TABLE blobs
(
    blobs_id INT IDENTITY PRIMARY KEY,
    type VARCHAR(255) NOT NULL,
    id VARCHAR(255) NOT NULL,
    "fileName" VARCHAR(255) NOT NULL,
    data VARBINARY(max) NOT NULL,
    CONSTRAINT blobs_id_unique UNIQUE (id)
);

exec sp_rename 'glue42SystemConfig.config', configs, 'COLUMN';

ALTER TABLE [glue42SystemConfig] ADD [weight] decimal(8, 2);

ALTER TABLE [users] ADD [layouts] NVARCHAR(max);

ALTER TABLE prefs
    ADD CONSTRAINT prefs__app__user__unique
        UNIQUE ([app], [user]);

ALTER TABLE layouts
    ADD CONSTRAINT layouts__name__type__owner__unique
    UNIQUE ([name], [type], [owner]);

-- Minimum required user permissions for the schema and the schema objects.
GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA :: [dbo] TO my_user;