Kraxe - Web Server for MS SQL Server

Contents

I. General information

Kraxe is designed to work with MS SQL databases via HTTP and receive data in JSON format.

Developed in Rust, the web server processes requests as quickly as possible, introducing minimal delays.

Asynchronous architecture allows you to process thousands of requests in parallel.

General work cycle:

FunctionsFree versionCommercial version
Asynchronous web server with easy configuration++
Run as a console application and as a Windows service++
Basic client authentication++
Logging requests and processing++
Customizable connection pool for MS SQL Server++
Serializing data fetch results into JSON++
Selecting multiple tables with one query-+
Number of rows in the result tableup to 100No limits
Using 'FOR XML' and 'FOR JSON' constructs in queries-+

For information about the commercial version and purchasing a license, write to: kraxe@exponenta.info

II. Installation

Download the current version.

Kraxe files and directories:

Kraxe has launch options:

> kraxe.exe -install --- Windows service installation.
> kraxe.exe -start --- start Windows service. The service must first be installed.
> kraxe.exe -stop --- stopping a Windows service.
> kraxe.exe -delete --- removing a Windows service.
> kraxe.exe -auto --- used in Windows service settings to start automatically.
> kraxe.exe -console --- launching the server from the command line as a regular console application.

An alternative option is to use the standard Windows sc.exe program to install the service.
In the command line running as administrator, run:

> sc.exe create Kraxe type= own start= auto error= normal binPath= "C:\...\kraxe\kraxe.exe -auto"
> sc.exe description Kraxe "Kraxe - Web Server for MS SQL"
> sc.exe start Kraxe

To remove a service:

> sc.exe stop Kraxe
> sc.exe delete Kraxe

To connect to the SQL server, Kraxe uses the TCP protocol. Access via this protocol must be enabled in the MS SQL settings. And also in the Windows firewall settings the corresponding port is open.

III. Configuration file kraxe.conf

Settings are stored in JSON format. At the top level, the file contains 4 sections:

{
  "global": {...}, // Kraxe Global Settings
  "http_server": {...}, // HTTP protocol settings
  "sql_server": {...}, // Settings for interaction with MS SQL Server
  "bindings": {...} // Relationships between resource URLs and SQL queries
  "parameters": {...} // Default variable values for SQL queries
}

III.a. Global Server Settings

"global": {
  "cached_queries": Bool,
  "log_level": String
}

cached_queries - boolean - in the case when the value is true, all SQL query texts are read from files into RAM once when the server starts. This allows you to speed up the processing of requests. If the value is false, the SQL query body is read from the file for each new incoming request. This behavior slows down the server somewhat, but allows you to dynamically change queries and debug texts without restarting the entire server. By default, if the parameter is not specified in the settings, the value will be set to true.

log_level - string - logging level. Can take the following values: "None" - logging is completely disabled, "Error" - log only critical errors, "Warning" - log errors and warnings, "Info" - record errors, warnings and information about processed requests, "Debug" - the most detailed information about the operation of the server. By default, if the parameter is not specified, the value will be set to "Info".

III.b. HTTP Server Settings

"http_server": {
  "host": "localhost",
  "port": 80,
  "auth": [
    {
      "scheme": "anonymous",
      "parameters": {
        "user_string": {
          "name": "@USER_STRING",
          "value": {
            "type": "String",
            "default": "User's non-overridable string."
          }
        }
      }
    },
    {
      "scheme": "basic",
      "name": "client",
      "password": "12345678"
    }
  ]
}

host - string - local IP address where the server will wait for incoming requests. Optional parameter. The default value is "127.0.0.1".

port - number - TCP port number on which the server listens for incoming HTTP requests. Optional parameter. The default value is 80.

auth - array of objects - description of service users. In order for the server to accept and process requests, at least one entry must be specified in the array.

auth.scheme - string - valid values: "anonymous", "basic". Server user authentication option. If in the "auth" section there is at least one entry where the authentication scheme "anonymous" is specified, requests to the server without authentication are allowed. Option "basic" - authentication in accordance with RFC 2617. Default value "basic".

auth.name - string - username for basic authentication. Doesn't matter for the "anonymous" schema.

auth.password - string - user password for basic authentication. Doesn't matter for the "anonymous" schema.

auth.parameters - object - a set of variables for SQL queries (for more information about parameters, see below). Optional. Valid for any authentication scheme.

III.с. SQL Server Settings

"sql_server": {
  "host": "192.168.100.9",
  "port": 1433,
  "database": "kraxe_test",
  "auth_method": "SqlServer",
  "login": "sa",
  "password": "12345678",
  "pool_size": 1,
  "date_offset": 0
}

host - string - SQL server ip address. Optional. The default value is "localhost".

port - number - SQL server port number. Optional parameter. The default value is 1433.

database - string - database name. Required parameter.

auth_method - string - SQL server authentication option. Possible values: “SqlServer” - built-in SQL Server authentication (default value), “Windows” - Windows user authentication. Not a required parameter.

login - string - database username. Optional. The default value is "sa".

password - string - database user password.

pool_size - number - the maximum number of simultaneous connections that Kraxe can open to exchange data with the SQL server. Optional. The default value is 1.

date_offset - number - the number of years that must be added when writing a date to the database and subtracted when reading a date from the database. Used by databases created from 1C:Enterprise. Optional. The default value is 0.

III.d. Description of HTTP server resources

The bindings section contains objects that describe possible HTTP requests to the server and parameters for their processing. The key of each entry is the http-method and URL of the resource that clients can request. For example, if a client requests the resource "GET http://mydomain.com/my_url_1?...", Kraxe will look in the bindings section for an entry with the resource name "/my_url_1" .

"bindings": {
  "GET /my_url_1": {
    "path": "page1.sql",
    "tables": [
      {
        "id": 0,
        "hide": false,
        "json": false
      },
      {
        "id": 1,
        "hide": false,
        "json": true
      }
    ],
    "parameters": {
      "my_bool": {
        "name": "@MY_BOOL",
        "value": {
          "type": "Bool",
          "default": false
        }
      },
      "my_number": {
        "name": "@MY_NUMBER",
        "value": {
          "type": "I16",
          "default": 157
      }
      }
    }
  }
}

path - string - the name of the file in the /queries directory that contains the text of the SQL query. Required parameter.

tables - array - description of the tables that the SQL server will return in response to the request. Optional parameter.

tables.id - number - index of the table in the results.

tables._N.hide - boolean - a sign of the need to hide the table and not be displayed in response to the client. If the value is true, the table will be obtained as a result of the SQL query, but will not be output to the resulting JSON. Optional parameter. The default value is false.

tables._N.json - boolean - a sign that the SQL request uses the “FOR JSON PATH” construction and the SQL server will return ready-made JSON that does not require serialization. Optional parameter. The default value is false.

parameters - object - Determining the values of request variables. You must list in this section all the variables specified in the SQL query and specify default values for them. These values will be used by Kraxe if parameters are not passed by the user in the HTTP request.

III.e. SQL query parameters (variables)

"parameters": {
 "begin": {
  "name": "@BEGIN",
  "value": {
   "type": "Date",
   "default": "2024-01-01T00:00:00+02:00"
  }
 },
 "end": {
  "name": "@END",
  "value": {
   "type": "Date",
   "default": "2024-12-31T23:59:59Z"
  }
 }
}

The values of variables in an SQL query are set from 4 sources:

Each parameter has a name, which is used in HTTP requests. Names are case sensitive.

name - string - contains the name of the variable as it will be used in the SQL query. In request texts, parameter names must begin with the '@' symbol.

value.type - string - value type. A pointer to Kraxe on how to interpret the value of an HTTP request to pass it into a SQL variable. Valid values:

value.default - string, number, boolean - the default value for the variable. If no corresponding parameter is specified in the custom HTTP request, then the SQL query variable will be set to this value.

An exceptional case is the @DATA variable. Kraken transfers the entire request body to this variable as a String value. The variable name is reserved and cannot be redefined.

© 2024, exponenta.info   For any questions, write to: kraxe@exponenta.info