IdStory Identity Manager

Information about setup for development:

MSSQL in docker¶

install docker via some tutorial (eq. ubuntu https://docs.docker.com/install/linux/docker-ce/ubuntu/)

then run:

docker pull microsoft/mssql-server-linux
docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=<YOUR_PASSWORD>' -p 1433:1433 -d microsoft/mssql-server-linux:latest

This install and run lasted MsSQL database (probably: SQL Server 2017 - 14)

MSSQL post prerequisites¶

after run MsSQL connect to DB via your favorite IDE and run these commands:

-- create database
CREATE DATABASE bcv_idm_storage;
GO
-- set active database
USE bcv_idm_storage;
GO
-- create login, login is used for connect to server, check policy is for development prupose (password idmadmin)
CREATE LOGIN idmadmin WITH PASSWORD = 'idmadmin', check_policy = off;
GO
-- create user, user will be used for connect to database
CREATE USER idmadmin FOR LOGIN idmadmin; 
GO
-- create schema
CREATE SCHEMA bcv_idm_storage AUTHORIZATION idmadmin;
GO
-- set default schema
ALTER USER idmadmin WITH DEFAULT_SCHEMA = bcv_idm_storage;
GO
-- grant permision for schema (grant all is deprecated)
GRANT ALTER, CONTROL, CREATE SEQUENCE, DELETE, EXECUTE, INSERT, REFERENCES, SELECT, TAKE OWNERSHIP, UPDATE, VIEW CHANGE TRACKING, VIEW DEFINITION ON SCHEMA::bcv_idm_storage TO idmadmin;
GO
-- grant create table to idmadmin
GRANT CREATE TABLE TO idmadmin;
GO
-- grant create view to idmadmin
GRANT CREATE VIEW TO idmadmin;

These commands create user and schema for czechIdM

Start CzechIdM¶

CzechIdM must be started with maven profile dev-mssql!

know issues¶

Tables by quartz and activity must have table and column names in upper case other tables has lower. For future implementation exists naming strategy (see file IdmMsSQLNamingStrategy.java) that allow transform names to uppercase.

Commit with flyway scripts and new profile: https://github.com/bcvsolutions/CzechIdMng/commit/4f246f27e34966eb04b6b7f7175a32d5e7534ca4

• hibernate.order_updates=true,
• hibernate.order_inserts=true,
• spring.datasource.maxOpenPreparedStatements=100,
• ...

every these 'fix' ends with deadlock or error:

2018-08-22 09:52:41.324  WARN 24586 --- [task-executor-5] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 1205, SQLState: 40001
2018-08-22 09:52:41.325 ERROR 24586 --- [task-executor-5] o.h.engine.jdbc.spi.SqlExceptionHelper   : Transaction (Process ID 54) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

• READ_COMMITTED_SNAPSHOT - locks are not used to protect the data from updates by other transactions,
• ALLOW_SNAPSHOT_ISOLATION

Documentation : https://docs.microsoft.com/en-us/sql/t-sql/statements/set-transaction-isolation-level-transact-sql?view=sql-server-2017#arguments

Microsoft recommends READ COMMITTED SNAPSHOT for most apps that need row versioning, so our application starts with use read committed snapshot.

After fix these issues with transnational and lock escalation I started with test errors:

• text as type is not supported for sql server (https://docs.microsoft.com/en-us/sql/t-sql/data-types/ntext-text-and-image-transact-sql?view=sql-server-2017) text was changed to nvarchar(MAX)
• isolation = Isolation.REPEATABLE_READ was removed from repositories: IdmAuthorizationPolicyRepository, IdmIdentityContractRepository and IdmRoleRepository,
• fix that except some default sorting,
• filtering by date - timestampt without timezone has datetime(6) sql server equivalent,
• the problem with datetime in test resources in acc module was solved,
• ALL tests are passed (finally)

• postrest type timestamp with timezone is in MsSQL datetime2,
• as TEXT please use nvarchar(max) - max is default constant,
• as classic String use nvarchar(255),
• character N in nvarchar means that string is in unicode,
• unique index in postgres allow duplicit null values, in MsSQL is required use filter in index - CREATE UNIQUE INDEX a ON columnA WHERE columnA IS NOT NULL;,
• jave Bolean is in mssql type bit,
• please dont setup isolation level on application layer,
• in project is possible use these drivers for mssql: mssql-jdbc, Sqljdbc4 and jTDS JDBC Driver,

There is probably main commit with chnages in tests: https://github.com/bcvsolutions/CzechIdMng/commit/0125329de02aa11a3582dddce8d5bb42a7168696
Complete feature is part of develop.

Reported script for create view works and index on varchar 2000 works on lasted version correctly, Petr probably use some old version (version information missing). For sure I removed both these things.

I also unite all flyway script for sql server to init script.

Please Radek could you create new flyway scripts and check settings? Thank you.

I created remaining change scripts, i did a test and review. It works, thx!