Updating the Decision Center database schema

Before you use the OpenID Connect server configuration feature in the Business console, you must update your Decision Center database schema to V8.10.3 if you are using the older version, to add the new feature to the server definitions.

Procedure

Run the SQL script (.sql) against your database to update the database schema.

Use one of the SQL scripts for your database type.

For Db2:

CREATE TABLE <dbSchema>.AUTHENTICATIONKIND (
	VALUE VARCHAR(30) NOT NULL,
	PRIMARY KEY (VALUE),
	DEPRECATED CHAR(1)
	);

INSERT INTO <dbSchema>.AUTHENTICATIONKIND (
	VALUE,
	DEPRECATED
	)
VALUES (
	'BASIC_AUTH',
	'F'
	);

INSERT INTO <dbSchema>.AUTHENTICATIONKIND (
	VALUE,
	DEPRECATED
	)
VALUES (
	'OAUTH',
	'F'
	);

ALTER TABLE <dbSchema>.SERVER ADD COLUMN AUTHENTICATIONKIND VARCHAR(30);

ALTER TABLE <dbSchema>.SERVER ADD CONSTRAINT SERVER_AUTHENTI FOREIGN KEY (AUTHENTICATIONKIND) REFERENCES <dbSchema>.AUTHENTICATIONKIND;

ALTER TABLE <dbSchema>.SERVER ADD COLUMN AUTHENTICATIONPROVIDER VARCHAR(128);

For Oracle database:

CREATE TABLE <dbSchema>.AUTHENTICATIONKIND (
	VALUE VARCHAR2(30) NOT NULL,
	PRIMARY KEY (VALUE),
	DEPRECATED CHAR(1)
	);

INSERT INTO <dbSchema>.AUTHENTICATIONKIND (
	VALUE,
	DEPRECATED
	)
VALUES (
	'BASIC_AUTH',
	'F'
	);

INSERT INTO <dbSchema>.AUTHENTICATIONKIND (
	VALUE,
	DEPRECATED
	)
VALUES (
	'OAUTH',
	'F'
	);

ALTER TABLE <dbSchema>.SERVER ADD AUTHENTICATIONKIND VARCHAR2 (30);

ALTER TABLE <dbSchema>.SERVER ADD CONSTRAINT SERVER_AUTHENTICATIONKIND FOREIGN KEY (AUTHENTICATIONKIND) REFERENCES <dbSchema>.AUTHENTICATIONKIND;

ALTER TABLE <dbSchema>.SERVER ADD AUTHENTICATIONPROVIDER VARCHAR2 (128);

For PostgreSQL:

CREATE TABLE <dbSchema>.authenticationkind (
	value VARCHAR(30) NOT NULL,
	PRIMARY KEY (value),
	deprecated bool
	);

INSERT INTO <dbSchema>.authenticationkind (
	VALUE,
	DEPRECATED
	)
VALUES (
	'BASIC_AUTH',
	FALSE
	);

INSERT INTO <dbSchema>.authenticationkind (
	VALUE,
	DEPRECATED
	)
VALUES (
	'OAUTH',
	FALSE
	);

ALTER TABLE <dbSchema>.SERVER ADD authenticationkind VARCHAR(30);

ALTER TABLE <dbSchema>.SERVER ADD CONSTRAINT server_authenticationkind FOREIGN KEY (authenticationkind) REFERENCES <dbSchema>.authenticationkind;

ALTER TABLE <dbSchema>.SERVER ADD authenticationprovider VARCHAR(128);

For MySQL:

CREATE TABLE <dbSchema>.AuthenticationKind (
	VALUE VARCHAR(30) NOT NULL,
	PRIMARY KEY (VALUE),
	DEPRECATED CHAR(1)
	) ENGINE = InnoDB;

INSERT INTO <dbSchema>.AuthenticationKind (
	VALUE,
	DEPRECATED
	)
VALUES (
	'BASIC_AUTH',
	'F'
	);

INSERT INTO <dbSchema>.AuthenticationKind (
	VALUE,
	DEPRECATED
	)
VALUES (
	'OAUTH',
	'F'
	);

ALTER TABLE <dbSchema>.Server ADD authenticationKind VARCHAR(30);

ALTER TABLE <dbSchema>.Server ADD CONSTRAINT Server_AuthenticationKind FOREIGN KEY (authenticationKind) REFERENCES <dbSchema>.AuthenticationKind (VALUE);

ALTER TABLE <dbSchema>.Server ADD authenticationProvider VARCHAR(128);

For SQL Server:

CREATE TABLE <dbSchema>.AuthenticationKind (
	VALUE NVARCHAR(30) NOT NULL,
	PRIMARY KEY NONCLUSTERED (VALUE),
	DEPRECATED CHAR(1)
	);

INSERT INTO <dbSchema>.AuthenticationKind (
	VALUE,
	DEPRECATED
	)
VALUES (
	N'BASIC_AUTH',
	N'F'
	);

INSERT INTO <dbSchema>.AuthenticationKind (
	VALUE,
	DEPRECATED
	)
VALUES (
	N'OAUTH',
	N'F'
	);

ALTER TABLE <dbSchema>.SERVER ADD authenticationKind NVARCHAR(30);

ALTER TABLE <dbSchema>.SERVER ADD CONSTRAINT Server_AuthenticationKind FOREIGN KEY (authenticationKind) REFERENCES <dbSchema>.AuthenticationKind;

ALTER TABLE <dbSchema>.SERVER ADD authenticationProvider NVARCHAR(128);

For Derby:

CREATE TABLE <dbSchema>.AUTHENTICATIONKIND (
	VALUE VARCHAR(30) NOT NULL,
	PRIMARY KEY (VALUE),
	DEPRECATED BOOLEAN
	);

INSERT INTO <dbSchema>.AUTHENTICATIONKIND (
	VALUE,
	DEPRECATED
	)
VALUES (
	'BASIC_AUTH',
	FALSE
	);

INSERT INTO <dbSchema>.AUTHENTICATIONKIND (
	VALUE,
	DEPRECATED
	)
VALUES (
	'OAUTH',
	FALSE
	);

ALTER TABLE <dbSchema>.SERVER ADD AUTHENTICATIONKIND VARCHAR(30);

ALTER TABLE <dbSchema>.SERVER ADD CONSTRAINT SERVER_AUTHENTI FOREIGN KEY (AUTHENTICATIONKIND) REFERENCES <dbSchema>.AUTHENTICATIONKIND;

ALTER TABLE <dbSchema>.SERVER ADD AUTHENTICATIONPROVIDER VARCHAR(128);

For H2:

CREATE TABLE PUBLIC.AUTHENTICATIONKIND (
	VALUE VARCHAR(30) NOT NULL,
	PRIMARY KEY (VALUE),
	DEPRECATED BOOLEAN
	);

INSERT INTO PUBLIC.AUTHENTICATIONKIND (
	VALUE,
	DEPRECATED
	)
VALUES (
	'BASIC_AUTH',
	FALSE
	);

INSERT INTO PUBLIC.AUTHENTICATIONKIND (
	VALUE,
	DEPRECATED
	)
VALUES (
	'OAUTH',
	FALSE
	);

ALTER TABLE PUBLIC.SERVER ADD AUTHENTICATIONKIND VARCHAR(30);

ALTER TABLE PUBLIC.SERVER ADD CONSTRAINT SERVER_AUTHENTICATIONKIND FOREIGN KEY (AUTHENTICATIONKIND) REFERENCES PUBLIC.AUTHENTICATIONKIND;

ALTER TABLE PUBLIC.SERVER ADD AUTHENTICATIONPROVIDER VARCHAR(128);