/* ---------------------------------------------------- */
/*  Generated by Enterprise Architect Version 13.5 		*/
/*  Created On : 27-bøe-2021 17:17:14 				*/
/*  DBMS       : SQLite 								*/
/* ---------------------------------------------------- */

/* Drop Tables */

DROP TABLE IF EXISTS 'CertificateType'
;

DROP TABLE IF EXISTS 'CertificateUsage'
;

DROP TABLE IF EXISTS 'PrivateKey'
;

DROP TABLE IF EXISTS 'Certificate'
;

/* Create Tables with Primary and Foreign Keys, Check and Unique Constraints */

CREATE TABLE 'CertificateType'
(
	'certificate_type_id' INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
	'certificate_type' TEXT NOT NULL
)
;

CREATE TABLE 'CertificateUsage'
(
	'certificate_usage_id' INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
	'certificate_usage' TEXT NOT NULL
)
;

CREATE TABLE 'PrivateKey'
(
	'private_key_id' INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
	'private_key' TEXT NOT NULL,
	'password' TEXT NULL
)
;

CREATE TABLE 'Certificate'
(
	'certificate_id' INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
	'common_name' TEXT NOT NULL,
	'valid_from' TEXT NOT NULL,
	'valid_to' TEXT NOT NULL,
	'pem_data' TEXT NOT NULL,
	'type_id' INTEGER NULL,
	'private_key_id' INTEGER NULL,
	'usage_id' INTEGER NULL,
	CONSTRAINT 'FK_Certificate_Type' FOREIGN KEY ('type_id') REFERENCES 'CertificateType' ('certificate_type_id') ON DELETE No Action ON UPDATE No Action,
	CONSTRAINT 'FK_Certificate_Usage' FOREIGN KEY ('usage_id') REFERENCES 'CertificateUsage' ('certificate_usage_id') ON DELETE No Action ON UPDATE No Action,
	CONSTRAINT 'FK_Parent_Certificate' FOREIGN KEY ('certificate_id') REFERENCES 'Certificate' ('certificate_id') ON DELETE No Action ON UPDATE No Action,
	CONSTRAINT 'FK_Private_Key' FOREIGN KEY ('private_key_id') REFERENCES 'PrivateKey' ('private_key_id') ON DELETE No Action ON UPDATE No Action
)
;