XLayer API v3.0

SeanR · 27-02-2020 11:51:56

There is a new XLayer endpoint (REST ONLY) that we can start using. This new XLayerAPI runs as a windows service and no longer relies on Tomcat being installed. You will need to download and install Java JDK 13.02.

XLayerAPI v3.0 can be downloaded from here.

This endpoint will write all payloads to a MYSQL\MariaDB database. It has both Asynchronous and Synchronous capabilities. Please check out the swagger documentation after you have installed it. the links can be found at the end of this pose

The included README.txt explains the following:

INSTALLATION

Follow the steps outlined below to get the XLayerAPI installed as a windows service.

Make sure you have Oracle Java JDK 13.02 installed (https://www.oracle.com/java/technologie … loads.html)
Unzip this archive to X:\SXI\XLayerAPI
Edit the XLayerAPI.xml configuration file that "spring.config.location" argument in the "arguments" element is pointing to the directory you extracted the archive to in the above step. (e.g. X:\SXI\XLayerAPI)
Open a command prompt as Administrator
Change directory to X:\SXI\XLayerAPI
Run the following: XLayerAPI.exe install

You can check in the "Services" Management Console that you now have a new Windows Service called "XLayerAPI".

CONFIGURATION

XLayerAPI sends all its transactions to a database. Depending on which database you use (either MySQL/MariaDB or MSSQL Server/Express)
Connect to the database and run the relevant init.sql script to create the database (|init_MYSQL.sql or init_MSSQL.sql)
NOTE: you do not need to create any tables as the XLayerAPI will automatically do this for you if they do not already exist.

The default database is MySQL / MariaDB however should you wish to use a MSSQL or a MSSQLExpress database then comment out the configuration in the application.properties file
under the comment

	#===============================================================================
	# MYSQL / MARIADB Database Settings
	# NOTE:
	#   COMMENT OUT this configuration if you wish to use a MS database
	#  (NB Make sure you uncomment the MSSQL settings below.
	#===============================================================================
        spring.jpa.hibernate.ddl-auto=update
        spring.datasource.url=jdbc:mysql://${MYSQL_HOST:localhost}:3306/sxirestapi
        #spring.datasource.url=jdbc:mysql://db01:3306/sxirestapi
        spring.datasource.username=root
        spring.datasource.password=sXi_Pass

and uncomment the configuration under the comment

	#===============================================================================
	# MSSQL Server / SQLExpress Database Settings
	# NOTE:
	#   UNCOMMENT if you wish to use a MS database (NB Make sure you comment out the
	#   MySQL settings above.
	#===============================================================================
        #spring.jpa.hibernate.ddl-auto=update
        #spring.datasource.url=jdbc:sqlserver://localhost:1433;databaseName=sxirestapi
        #spring.datasource.username=sa
        #spring.datasource.password=sXi_Pass
        #spring.datasource.driverClassName=com.microsoft.sqlserver.jdbc.SQLServerDriver
        #spring.jpa.show-sql=true

Should you wish to change some of the default settings for XLayerAPI you can do so in the application.properties file.
NOTE: Once you have made changes to this file you will need to restart the application.

Other settings that should be checked are:

sync.transaction.timeout - This is the number of seconds the session will waid for a response before sending a failed message to the client. (Default 60 seconds)

server.port - This is the port that the XLayerAPI will listen on. (Default = 9788)

spring.datasource.url - The MYSQL connection string needed to connect to the "sxirestapi" database.
(Default = jdbc:mysql://${MYSQL_HOST:localhost}:3306/sxirestapi)
- or -
- The MSSQL connection string needed to connect to the "sxirestapi" database.
(Default = jdbc:sqlserver://localhost:1433;databaseName=sxirestapi)

If you want to use a different database name then simply change "sxirestapi" to the name you wish to use in the relevant init.sql file and in the connection string.

USEFUL LINKS

SWAGGER Documentation = https://localhost:9788/swagger-ui.html
Spring Management = https://localhost:9789/actuator
= https://localhost:9789/actuator/health

SeanR · 27-02-2020 12:13:20

If you want to play with this XLayerAPI really quickly and want a super fast database setup use docker and follow these steps. This will create a MySQL container on your machine with the default ports exposed (3306) as well as create a new database in MySQL called "sxirestapi". You should also be able to connect to this container using a tool like DBeaver using the following credentials:

Username: root
Password: sXi_Pass

To setup this container follow these steps:

Create a directory on your machine E.g. dockerImgCfgs

Create a file called docker-compose.yml inside that directory and paste the following into it:

version: '3'

services:
  db01:
    image: mysql:5.7.29
    container_name: xlayer-api-db01
    hostname: xlayer-api-db01
    networks:
      - sxiws_net
    ports:
      - 3306:3306
    volumes:
      # ===== Windows Host =====
      - C:\dockerImgCfgs\DB01:/var/lib/mysql
      - C:\dockerImgCfgs\my.cnf:/etc/mysql/my.cnf
      - C:\dockerImgCfgs\init.sql:/init.sql
      # ====== Linux Host ======
      #- ./dockerCfg/my.cnf:/etc/mysql/my.cnf
      #- ./dockerCfg/init.sql:/init.sql
      #- /opt/SXI/SXIDB:/var/lib/mysql
    environment:
      - MYSQL_DATABASE=mysql
      - MYSQL_ROOT_PASSWORD=sXi_Pass
    command:
      'mysqld --innodb-flush-method=fsync --init-file=/init.sql'

networks:
  sxiws_net:
    driver: bridge

In the same directory create a file called "init.sql" and paste the following into it:
```
CREATE DATABASE IF NOT EXISTS sxirestapi CHARACTER SET utf8 COLLATE utf8_general_ci;
```

Also in the same directory create a my.cnf file and paste the following into it (you MAY need to go through this file and make adjustments specific to your machine althouhg I have keep things very generic so you shouldn't have to make changes):

# MariaDB/MySQL database server configuration file.
#
# You can copy this file to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

# This will be passed to all mysql clients
# It has been reported that passwords should be enclosed with ticks/quotes
# escpecially if they contain "#" chars...
# Remember to edit /etc/mysql/debian.cnf when changing the socket location.
[client]
port		= 3306
socket		= /var/run/mysqld/mysqld.sock

# Here is entries for some specific programs
# The following values assume you have at least 32M ram

# This was formally known as [safe_mysqld]. Both versions are currently parsed.
[mysqld_safe]
socket		= /var/run/mysqld/mysqld.sock
nice		= 0

[mysqld]
#
# * Basic Settings
#
#user		= mysql
pid-file	= /var/run/mysqld/mysqld.pid
socket		= /var/run/mysqld/mysqld.sock
port		= 3306
basedir		= /usr
datadir		= /var/lib/mysql
tmpdir		= /tmp
lc_messages_dir	= /usr/share/mysql
lc_messages	= en_US
skip-external-locking
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address		= 0.0.0.0
#
# * Fine Tuning
#
max_connections		= 100
connect_timeout		= 5
wait_timeout		= 600
max_allowed_packet	= 16M
thread_cache_size       = 128
sort_buffer_size	= 4M
bulk_insert_buffer_size	= 16M
tmp_table_size		= 32M
max_heap_table_size	= 32M
#
# * MyISAM
#
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched. On error, make copy and try a repair.
myisam_recover_options = BACKUP
key_buffer_size		= 128M
#open-files-limit	= 2000
table_open_cache	= 400
myisam_sort_buffer_size	= 512M
concurrent_insert	= 2
read_buffer_size	= 2M
read_rnd_buffer_size	= 1M
#
# * Query Cache Configuration
#
# Cache only tiny result sets, so we can fit more in the query cache.
query_cache_limit		= 128K
query_cache_size		= 64M
# for more write intensive setups, set to DEMAND or OFF
#query_cache_type		= DEMAND
#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# As of 5.1 you can enable the log at runtime!
#general_log_file        = /var/log/mysql/mysql.log
#general_log             = 1
#
# Error logging goes to syslog due to /etc/mysql/conf.d/mysqld_safe_syslog.cnf.
#
# we do want to know about network errors and such
#log_warnings		= 2
#
# Enable the slow query log to see queries with especially long duration
#slow_query_log[={0|1}]
slow_query_log_file	= /var/log/mysql/mariadb-slow.log
long_query_time = 10
#log_slow_rate_limit	= 1000
#log_slow_verbosity	= query_plan

#log-queries-not-using-indexes
#log_slow_admin_statements
#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
#       other settings you may need to change.
#server-id		= 1
#report_host		= master1
#auto_increment_increment = 2
#auto_increment_offset	= 1
#log_bin			= /var/log/mysql/mariadb-bin
#log_bin_index		= /var/log/mysql/mariadb-bin.index
# not fab for performance, but safer
#sync_binlog		= 1
expire_logs_days	= 10
max_binlog_size         = 100M
# slaves
#relay_log		= /var/log/mysql/relay-bin
#relay_log_index	= /var/log/mysql/relay-bin.index
#relay_log_info_file	= /var/log/mysql/relay-bin.info
#log_slave_updates
#read_only
#
# If applications support it, this stricter sql_mode prevents some
# mistakes like inserting invalid dates etc.
#sql_mode		= NO_ENGINE_SUBSTITUTION,TRADITIONAL
#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
default_storage_engine	= InnoDB
# you can't just change log file size, requires special procedure
#innodb_log_file_size	= 50M
innodb_buffer_pool_size	= 256M
innodb_log_buffer_size	= 8M
innodb_file_per_table	= 1
innodb_open_files	= 400
innodb_io_capacity	= 400
innodb_flush_method	= O_DIRECT
#
# * Security Features
#
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
#
# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
#
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem

#
# * Galera-related settings
#
[galera]
# Mandatory settings
#wsrep_on=ON
#wsrep_provider=
#wsrep_cluster_address=
#binlog_format=row
#default_storage_engine=InnoDB
#innodb_autoinc_lock_mode=2
#
# Allow server to accept connections on all interfaces.
#
bind-address=0.0.0.0
#
# Optional setting
#wsrep_slave_threads=1
#innodb_flush_log_at_trx_commit=0

[mysqldump]
quick
quote-names
max_allowed_packet	= 16M

[mysql]
#no-auto-rehash	# faster start of mysql but no tab completion

[isamchk]
key_buffer		= 16M

#
# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#
!include /etc/mysql/mariadb.cnf
!includedir /etc/mysql/conf.d/

Once you have these files created run the following:
```
docker-compose up
```

NOTE:
If you wish to get rid of the container that was launched simply run the following:

docker-compose down

Remember to delete the DBs file system that will have been created in C:\dockerImgCfgs\DB01

SeanR · 02-03-2020 19:35:05

You can also connect to a MSSQLServer/EXPRESS installation should you prefer.

This can create a SQLExpress container on your machine with the default ports exposed (1433). You can also connect to a database that is already installed. You will need to connect to this database using a tool like DBeaver using the following credentials:

Username: sa
Password: sXi_Pass (Or whatever is already configured for an existing installation)

To setup a MSSQL EXPRESS container follow these steps:

Create a directory on your machine E.g. dockerImgCfgs

Create a file called docker-compose.yml inside that directory and paste the following into it:

version: '3'

services:
  db01:
    image: mcr.microsoft.com/mssql/server:2017-latest
    container_name: xlayer-api-sqlexpress01
    hostname: xlayer-api-sqlexpress01
    networks:
      - sxiws_net
    ports:
      - 1433:1433
    volumes:
      # ===== Windows Host =====
      - C:\dockerImgCfgs\SQLEXPRESS01:/var/opt/mssql/data
    environment:
      - ACCEPT_EULA=Y
      - SA_PASSWORD=sXi_Pass
      - MSSQL_PID=Express

networks:
  sxiws_net:
    driver: bridge

Run the following to start your container
```
docker-compose up
```
Connect to this database container and create a "sxirestapi" database.
Once the database is created you can start the XLayerAPI and it will create the relevant tables.

NOTE:
If you wish to get rid of the container that was launched simply run the following:

docker-compose down

Remember to delete the DBs file system that will have been created in C:\dockerImgCfgs\SQLEXPRESS01

SXI Forum

#1 27-02-2020 11:51:56

XLayer API v3.0

#2 27-02-2020 12:13:20

Re: XLayer API v3.0

#3 02-03-2020 19:35:05

Re: XLayer API v3.0

Board footer