Configuration and database changes

Configuration changes

C:\KodiakServicesServer\Configs\KodiakServices.config

MembershipService has a new property (ExternalUserRepository) and now looks like this.

<object id="MembershipService"
 singleton="false"
 type="Kodi.Kodiak.Services.MembershipService, Kodi.Kodiak.Services">
 <property name="UserRepository" ref="UserRepository"/>
 <property name="GroupRepository" ref="GroupRepository"/>
 <property name="AccessRuleListRepository" ref="AccessRuleListRepository"/>
 <property name="ExternalUserRepository" ref="ExternalUserRepository"/>
 <property name="UserEntityBuilder" ref="UserEntityBuilder"/>
 <property name="GroupEntityBuilder" ref="GroupEntityBuilder"/>

</object>

Database changes

Kodiak OMS 1.8 has a few new database fields and one new table.

Note that for Oracle the Kodiak User needs to be granted read/write access to the new USER_SYSTEM_ACCESS table.

MSSQL

ALTER TABLE USERS ADD TRADER_TYPE INT null;
ALTER TABLE USERS ADD LAST_MODIFIED DATETIME null;
ALTER TABLE USERS ADD COMMENTS varchar(500) null;

create table USERS_SYSTEM_ACCESS (
    ID_USER_ACCESS INT IDENTITY NOT NULL,
    USER_ID INT null,
    SYSTEM_ACCESS INT null,
    SYSTEMS INT null,
    primary key (ID_USER_ACCESS)
)
alter table USERS_SYSTEM_ACCESS 
    add constraint ACCESS_USER_FK 
    foreign key (USER_ID) 
    references USERS

alter table USERS_SYSTEM_ACCESS 
    add constraint USER_SYSTEM_ACCESS_FK 
    foreign key (SYSTEMS) 
    references USERS

UPDATE USERS SET LAST_MODIFIED = SELECT GETDATE();

ALTER TABLE EXECUTIONS ADD EXECUTION_REPORT_DATE DATETIME;
 
-- SET EXECUTION_REPORT_DATE to TRANSACTION_TIME but truncate the time part.
UPDATE EXECUTIONS SET EXECUTION_REPORT_DATE = cast(convert(char(11), TRANSACTION_TIME, 113) as DATETIME);
	
-- EXECUTION_REPORT_DATE should never be null
ALTER TABLE EXECUTIONS 			
ALTER COLUMN EXECUTION_REPORT_DATE DATETIME NOT NULL;			
		
ALTER TABLE ORDERS ADD LAST_TRANSACTION_TIME DATETIME;		

ORACLE

ALTER TABLE USERS ADD TRADER_TYPE NUMBER(10,0) null;
ALTER TABLE USERS ADD LAST_MODIFIED TIMESTAMP null;
ALTER TABLE USERS ADD COMMENTS varchar2(500) null;

UPDATE USERS SET LAST_MODIFIED = SELECT sysdate from dual;

create sequence USERS_ACCESS_SEQ;

create table USERS_SYSTEM_ACCESS (
    ID_USER_ACCESS NUMBER(10,0) not null,
    USER_ID NUMBER(10,0),
    SYSTEM_ACCESS NUMBER(10,0),
    SYSTEMS NUMBER(10,0),
    primary key (ID_USER_ACCESS)
);
alter table USERS_SYSTEM_ACCESS 
    add constraint ACCESS_USER_FK 
    foreign key (USER_ID) 
    references USERS;

alter table USERS_SYSTEM_ACCESS 
    add constraint USER_SYSTEM_ACCESS_FK 
    foreign key (SYSTEMS) 
    references USERS;

ALTER TABLE EXECUTIONS ADD ("EXECUTION_REPORT_DATE" TIMESTAMP(4));
COMMENT ON COLUMN EXECUTIONS."EXECUTION_REPORT_DATE" IS 'Shows the date that the execution report was created.';	
 
-- SET EXECUTION_REPORT_DATE to TRANSACTION_TIME but truncate the time part. 
UPDATE EXECUTIONS SET EXECUTION_REPORT_DATE = trunc(TRANSACTION_TIME);
 
-- EXECUTION_REPORT_DATE should never be null 
ALTER TABLE EXECUTIONS MODIFY 
(
  "EXECUTION_REPORT_DATE" TIMESTAMP(4) NOT NULL
);
	
ALTER TABLE ORDERS ADD ("LAST_TRANSACTION_TIME" TIMESTAMP(4));		
COMMENT ON COLUMN ORDERS."LAST_TRANSACTION_TIME" IS 'Indicates the last transaction of the execution reports that belong to the order.';