1
|
/* ---------------------------------------------------- */
|
2
|
/* Generated by Enterprise Architect Version 13.5 */
|
3
|
/* Created On : 27-b?e-2021 17:17:14 */
|
4
|
/* DBMS : SQLite */
|
5
|
/* ---------------------------------------------------- */
|
6
|
|
7
|
/* Drop Tables */
|
8
|
|
9
|
DROP TABLE IF EXISTS 'CertificateType'
|
10
|
;
|
11
|
|
12
|
DROP TABLE IF EXISTS 'CertificateUsage'
|
13
|
;
|
14
|
|
15
|
DROP TABLE IF EXISTS 'PrivateKey'
|
16
|
;
|
17
|
|
18
|
DROP TABLE IF EXISTS 'Certificate'
|
19
|
;
|
20
|
|
21
|
/* Create Tables with Primary and Foreign Keys, Check and Unique Constraints */
|
22
|
|
23
|
CREATE TABLE 'CertificateType'
|
24
|
(
|
25
|
'certificate_type_id' INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
|
26
|
'certificate_type' TEXT NOT NULL
|
27
|
)
|
28
|
;
|
29
|
|
30
|
CREATE TABLE 'CertificateUsage'
|
31
|
(
|
32
|
'certificate_usage_id' INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
|
33
|
'certificate_usage' TEXT NOT NULL
|
34
|
)
|
35
|
;
|
36
|
|
37
|
CREATE TABLE 'PrivateKey'
|
38
|
(
|
39
|
'private_key_id' INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
|
40
|
'private_key' TEXT NOT NULL,
|
41
|
'password' TEXT NULL
|
42
|
)
|
43
|
;
|
44
|
|
45
|
CREATE TABLE 'Certificate'
|
46
|
(
|
47
|
'certificate_id' INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
|
48
|
'common_name' TEXT NOT NULL,
|
49
|
'valid_from' TEXT NOT NULL,
|
50
|
'valid_to' TEXT NOT NULL,
|
51
|
'pem_data' TEXT NOT NULL,
|
52
|
'type_id' INTEGER NULL,
|
53
|
'private_key_id' INTEGER NULL,
|
54
|
'usage_id' INTEGER NULL,
|
55
|
CONSTRAINT 'FK_Certificate_Type' FOREIGN KEY ('type_id') REFERENCES 'CertificateType' ('certificate_type_id') ON DELETE No Action ON UPDATE No Action,
|
56
|
CONSTRAINT 'FK_Certificate_Usage' FOREIGN KEY ('usage_id') REFERENCES 'CertificateUsage' ('certificate_usage_id') ON DELETE No Action ON UPDATE No Action,
|
57
|
CONSTRAINT 'FK_Parent_Certificate' FOREIGN KEY ('certificate_id') REFERENCES 'Certificate' ('certificate_id') ON DELETE No Action ON UPDATE No Action,
|
58
|
CONSTRAINT 'FK_Private_Key' FOREIGN KEY ('private_key_id') REFERENCES 'PrivateKey' ('private_key_id') ON DELETE No Action ON UPDATE No Action
|
59
|
)
|
60
|
;
|