4. Create Database

Create Database

A local database is needed for enclave server to store the data such as user verification history in VASP’s environment. While MySQL is recommended, other Relational Database Management System (RDMS) databases such as Oracle, Postgresql, MSSQL are supported. The script needed to create the table required as below:

Create "verifications" table

MySQL
PostgreSQL
MSSQL
Oracle
1
CREATE TABLE `verifications` (
2
`verification_id` bigint(20) unsigned NOT NULL COMMENT 'Verification ID',
3
`verification_uuid` varchar(40) NOT NULL COMMENT 'Verification UUID',
4
`oi_public_key` varchar(256) DEFAULT NULL COMMENT 'Ordering Institute Public Key',
5
`oi_private_key` varchar(256) DEFAULT NULL COMMENT 'Ordering Institute Private Key',
6
`bi_public_key` varchar(256) DEFAULT NULL COMMENT 'Beneficiary Institute Public Key',
7
`bi_private_key` varchar(256) DEFAULT NULL COMMENT 'Beneficiary Institute Private Key',
8
`status` enum('WAIT', 'REQUESTED-PUBLIC-KEY', 'TRANSMITTED-PUBLIC-KEY', 'TRANSMITTED-PRIVATE-DATA', 'TRANSMITTED-VERIF-RESULT', 'TRANSMITTED-TX-RESULT', 'CONFIRMED-BY-BENE', 'ERROR') NOT NULL DEFAULT 'WAIT' COMMENT 'Status',
9
`result` enum('WAIT', 'VERIFIED', 'UNKNOWN', 'DENIED', 'ERROR', 'PENDING') NOT NULL DEFAULT 'WAIT' COMMENT 'Verification Result',
10
`reason` varchar(256) DEFAULT NULL COMMENT 'Reason',
11
`symbol` varchar(16) DEFAULT NULL COMMENT 'Symbol',
12
`amount` varchar(128) DEFAULT NULL COMMENT 'Amount',
13
`tx_hash` varchar(128) DEFAULT NULL COMMENT 'Tx hash',
14
`from_vasp_id` bigint(20) unsigned DEFAULT NULL COMMENT 'Ordering Institute Vasp ID',
15
`from_alliance_id` bigint(20) unsigned DEFAULT NULL COMMENT 'Ordering Institute Alliance ID',
16
`from_account_number` varchar(256) DEFAULT NULL COMMENT 'Originator Account Address or ID',
17
`ivms101_originator` text(65535) DEFAULT NULL COMMENT 'Originator information',
18
`ivms101_originator_vasp` text(65535) DEFAULT NULL COMMENT 'Originator VASP information',
19
`to_vasp_id` bigint(20) unsigned DEFAULT NULL COMMENT 'Beneficiary Institute Vasp ID',
20
`to_alliance_id` bigint(20) unsigned DEFAULT NULL COMMENT 'Beneficiary Institute Alliance ID',
21
`to_account_number` varchar(256) DEFAULT NULL COMMENT 'Beneficiary Account Address or ID',
22
`ivms101_beneficiary` text(65535) DEFAULT NULL COMMENT 'Beneficiary information',
23
`ivms101_beneficiary_vasp` text(65535) DEFAULT NULL COMMENT 'Beneficiary VASP information',
24
`req_private_data` text(65535) DEFAULT NULL COMMENT 'Private data for verification',
25
`res_private_data` text(65535) DEFAULT NULL COMMENT 'Private data for verification result',
26
`verified_at` datetime DEFAULT NULL COMMENT 'Verified at.',
27
`ordered_at` datetime DEFAULT NULL COMMENT 'Ordered at.',
28
`created_at` datetime DEFAULT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Created at.',
29
PRIMARY KEY (`verification_id`),
30
UNIQUE KEY `uk_verifications_verification_uuid` (`verification_uuid`),
31
INDEX `idx_verifications_from_account` (`from_account_number`),
32
INDEX `idx_verifications_to_account` (`to_account_number`)
33
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
34
Copied!
1
-- charset setting
2
vega_enclave_oi=> set server_encoding='UTF8';
3
vega_enclave_oi=> set client_encoding='UTF8';
4
vega_enclave_bi=> set server_encoding='UTF8';
5
vega_enclave_bi=> set client_encoding='UTF8';
6
---
7
8
CREATE TYPE enum_status AS ENUM ('WAIT', 'REQUESTED-PUBLIC-KEY', 'TRANSMITTED-PUBLIC-KEY', 'TRANSMITTED-PRIVATE-DATA', 'TRANSMITTED-VERIF-RESULT', 'TRANSMITTED-TX-RESULT', 'CONFIRMED-BY-BENE', 'ERROR');
9
CREATE TYPE enum_result AS ENUM ('WAIT', 'VERIFIED', 'UNKNOWN', 'DENIED', 'ERROR', 'PENDING');
10
11
CREATE TABLE verifications (
12
verification_id numeric(20) NOT NULL PRIMARY KEY,
13
verification_uuid varchar(40) NOT NULL,
14
UNIQUE(verification_uuid),
15
oi_public_key varchar(256) DEFAULT NULL,
16
oi_private_key varchar(256) DEFAULT NULL,
17
bi_public_key varchar(256) DEFAULT NULL,
18
bi_private_key varchar(256) DEFAULT NULL,
19
status enum_status DEFAULT 'WAIT',
20
result enum_result DEFAULT 'WAIT',
21
reason varchar(256) DEFAULT NULL,
22
symbol varchar(16) DEFAULT NULL,
23
amount varchar(128) DEFAULT NULL,
24
tx_hash varchar(128) DEFAULT NULL,
25
from_vasp_id numeric(20) DEFAULT NULL,
26
from_alliance_id numeric(20) DEFAULT NULL,
27
from_account_number varchar(256) DEFAULT NULL,
28
ivms101_originator varchar(65535) DEFAULT NULL,
29
ivms101_originator_vasp varchar(65535) DEFAULT NULL,
30
to_vasp_id numeric(20) DEFAULT NULL,
31
to_alliance_id numeric(20) DEFAULT NULL,
32
to_account_number varchar(256) DEFAULT NULL,
33
ivms101_beneficiary varchar(65535) DEFAULT NULL,
34
ivms101_beneficiary_vasp varchar(65535) DEFAULT NULL,
35
req_private_data varchar(65535) DEFAULT NULL,
36
res_private_data varchar(65535) DEFAULT NULL,
37
verified_at timestamp DEFAULT NULL,
38
ordered_at timestamp DEFAULT NULL,
39
created_at timestamp DEFAULT CURRENT_TIMESTAMP
40
);
41
42
CREATE INDEX idx_verifications_from_account ON verifications(from_account_number);
43
CREATE INDEX idx_verifications_to_account ON verifications(to_account_number);
Copied!
1
CREATE TABLE verifications (
2
verification_id bigint check (verification_id > 0) NOT NULL PRIMARY KEY,
3
verification_uuid nvarchar(40) NOT NULL UNIQUE,
4
oi_public_key nvarchar(256) DEFAULT NULL,
5
oi_private_key nvarchar(256) DEFAULT NULL,
6
bi_public_key nvarchar(256) DEFAULT NULL,
7
bi_private_key nvarchar(256) DEFAULT NULL,
8
status nvarchar(60) DEFAULT 'WAIT' NOT NULL check (status in ('WAIT', 'REQUESTED-PUBLIC-KEY', 'TRANSMITTED-PUBLIC-KEY', 'TRANSMITTED-PRIVATE-DATA', 'TRANSMITTED-VERIF-RESULT', 'TRANSMITTED-TX-RESULT', 'CONFIRMED-BY-BENE', 'ERROR')),
9
result nvarchar(20) DEFAULT 'WAIT' NOT NULL check (result in ('WAIT', 'VERIFIED', 'UNKNOWN', 'DENIED', 'ERROR', 'PENDING')),
10
reason nvarchar(256) DEFAULT NULL,
11
symbol nvarchar(16) DEFAULT NULL,
12
amount nvarchar(128) DEFAULT NULL,
13
tx_hash nvarchar(128) DEFAULT NULL,
14
from_vasp_id bigint check (from_vasp_id > 0) DEFAULT NULL,
15
from_alliance_id bigint check (from_alliance_id > 0) DEFAULT NULL,
16
from_account_number nvarchar(256) DEFAULT NULL,
17
ivms101_originator nvarchar(MAX) DEFAULT NULL,
18
ivms101_originator_vasp nvarchar(MAX) DEFAULT NULL,
19
to_vasp_id bigint check (to_vasp_id > 0) DEFAULT NULL,
20
to_alliance_id bigint check (to_alliance_id > 0) DEFAULT NULL,
21
to_account_number nvarchar(256) DEFAULT NULL,
22
ivms101_beneficiary nvarchar(MAX) DEFAULT NULL,
23
ivms101_beneficiary_vasp nvarchar(MAX) DEFAULT NULL,
24
req_private_data nvarchar(MAX) DEFAULT NULL,
25
res_private_data nvarchar(MAX) DEFAULT NULL,
26
verified_at datetime DEFAULT NULL,
27
ordered_at datetime DEFAULT NULL,
28
created_at datetime DEFAULT CURRENT_TIMESTAMP
29
);
30
31
CREATE INDEX idx_verifications_from_account ON verifications(from_account_number);
32
CREATE INDEX idx_verifications_to_account ON verifications(to_account_number);
Copied!
1
-- charset setting
2
UPDATE sys.props$ set value$='UTF-8' where name='NLS_CHARACTERSET';
3
---
4
5
CREATE TABLE "verifications" (
6
"verification_id" number(20) check ("verification_id" > 0) NOT NULL,
7
"verification_uuid" varchar2(40) NOT NULL,
8
"oi_public_key" varchar2(256) DEFAULT NULL,
9
"oi_private_key" varchar2(256) DEFAULT NULL,
10
"bi_public_key" varchar2(256) DEFAULT NULL,
11
"bi_private_key" varchar2(256) DEFAULT NULL,
12
"status" varchar2(60) DEFAULT 'WAIT' NOT NULL check ("status" in ('WAIT', 'REQUESTED-PUBLIC-KEY', 'TRANSMITTED-PUBLIC-KEY', 'TRANSMITTED-PRIVATE-DATA', 'TRANSMITTED-VERIF-RESULT', 'TRANSMITTED-TX-RESULT', 'CONFIRMED-BY-BENE', 'ERROR')),
13
"result" varchar2(20) DEFAULT 'WAIT' NOT NULL check ("result" in ('WAIT', 'VERIFIED', 'UNKNOWN', 'DENIED', 'ERROR', 'PENDING')),
14
"reason" varchar2(256) DEFAULT NULL,
15
"symbol" varchar2(16) DEFAULT NULL,
16
"amount" varchar2(128) DEFAULT NULL,
17
"tx_hash" varchar2(128) DEFAULT NULL,
18
"from_vasp_id" number(20) DEFAULT NULL check ("from_vasp_id" > 0),
19
"from_alliance_id" number(20) DEFAULT NULL check ("from_alliance_id" > 0),
20
"from_account_number" varchar2(256) DEFAULT NULL,
21
"ivms101_originator" clob DEFAULT NULL,
22
"ivms101_originator_vasp" clob DEFAULT NULL,
23
"to_vasp_id" number(20) DEFAULT NULL check ("to_vasp_id" > 0),
24
"to_alliance_id" number(20) DEFAULT NULL check ("to_alliance_id" > 0),
25
"to_account_number" varchar2(256) DEFAULT NULL,
26
"ivms101_beneficiary" clob DEFAULT NULL,
27
"ivms101_beneficiary_vasp" clob DEFAULT NULL,
28
"req_private_data" clob DEFAULT NULL,
29
"res_private_data" clob DEFAULT NULL,
30
"verified_at" date DEFAULT NULL,
31
"ordered_at" date DEFAULT NULL,
32
"created_at" date DEFAULT CURRENT_TIMESTAMP,
33
CONSTRAINT "pk_verification_id" PRIMARY KEY ("verification_id"),
34
CONSTRAINT "uk_verification_uuid" UNIQUE("verification_uuid")
35
);
36
37
CREATE INDEX "idx_verifications_from_account" ON "verifications"("from_account_number");
38
CREATE INDEX "idx_verifications_to_account" ON "verifications"("to_account_number");
Copied!

Create "pre-screening" table

MySQL
PostgreSQL
MSSQL
Oracle
1
CREATE TABLE `pre_screenings` (
2
`pre_screening_id` bigint(20) unsigned NOT NULL COMMENT 'Pre-Screening request ID',
3
`pre_screening_asset` varchar(256) DEFAULT NULL COMMENT 'The asset associated with the address',
4
`pre_screening_address` varchar(256) DEFAULT NULL COMMENT 'Address',
5
`cluster_name` varchar(256) DEFAULT NULL COMMENT 'The name of the named cluster',
6
`cluster_category` varchar(256) DEFAULT NULL COMMENT 'The category of the named cluster',
7
`rating` enum('UNKNOWN', 'HIGH', 'MEDIUM', 'LOW') DEFAULT NULL COMMENT 'The risk rating of the known recipient address',
8
`created_at` datetime DEFAULT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Created at.',
9
PRIMARY KEY (`pre_screening_id`),
10
INDEX `idx_pre_screenings_address` (`pre_screening_address`),
11
INDEX `idx_pre_screenings_asset` (`pre_screening_asset`)
12
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Copied!
1
-- charset setting
2
vega_enclave_oi=> set server_encoding='UTF8';
3
vega_enclave_oi=> set client_encoding='UTF8';
4
vega_enclave_bi=> set server_encoding='UTF8';
5
vega_enclave_bi=> set client_encoding='UTF8';
6
---
7
8
CREATE TYPE enum_rating AS ENUM ('UNKNOWN', 'HIGH', 'MEDIUM', 'LOW');
9
10
CREATE TABLE pre_screenings (
11
pre_screening_id numeric(20) NOT NULL PRIMARY KEY,
12
pre_screening_asset varchar(256) DEFAULT NULL,
13
pre_screening_address varchar(256) DEFAULT NULL,
14
cluster_name varchar(256) DEFAULT NULL,
15
cluster_category varchar(256) DEFAULT NULL,
16
rating enum_rating DEFAULT NULL,
17
created_at timestamp DEFAULT CURRENT_TIMESTAMP
18
);
19
20
CREATE INDEX idx_pre_screenings_address ON pre_screenings(pre_screening_address);
21
CREATE INDEX idx_pre_screenings_asset ON pre_screenings(pre_screening_asset);
Copied!
1
CREATE TABLE pre_screenings (
2
pre_screening_id bigint check (pre_screening_id > 0) NOT NULL PRIMARY KEY,
3
pre_screening_asset nvarchar(256) DEFAULT NULL,
4
pre_screening_address nvarchar(256) DEFAULT NULL,
5
cluster_name nvarchar(256) DEFAULT NULL,
6
cluster_category nvarchar(256) DEFAULT NULL,
7
rating nvarchar(10) DEFAULT NULL check (rating in ('UNKNOWN', 'HIGH', 'MEDIUM', 'LOW')),
8
created_at datetime DEFAULT CURRENT_TIMESTAMP
9
);
10
11
CREATE INDEX idx_pre_screenings_address ON pre_screenings(pre_screening_address);
12
CREATE INDEX idx_pre_screenings_asset ON pre_screenings(pre_screening_asset);
Copied!
1
-- charset setting
2
UPDATE sys.props$ set value$='UTF-8' where name='NLS_CHARACTERSET';
3
---
4
5
CREATE TABLE "pre_screenings" (
6
"pre_screening_id" number(20) check ("pre_screening_id" > 0) NOT NULL,
7
"pre_screening_asset" varchar2(256) DEFAULT NULL,
8
"pre_screening_address" varchar2(256) DEFAULT NULL,
9
"cluster_name" varchar2(256) DEFAULT NULL,
10
"cluster_category" varchar2(256) DEFAULT NULL,
11
"rating" varchar2(20) DEFAULT NULL check ("rating" in ('UNKNOWN', 'HIGH', 'MEDIUM', 'LOW')),
12
"created_at" date DEFAULT CURRENT_TIMESTAMP,
13
CONSTRAINT "pre_screening_id" PRIMARY KEY ("pre_screening_id")
14
);
15
16
CREATE INDEX "idx_pre_screenings_address" ON "pre_screenings"("pre_screening_address");
17
CREATE INDEX "idx_pre_screenings_asset" ON "pre_screenings"("pre_screening_asset");
Copied!
Last modified 5mo ago