aswi2021jmsd-gitlab4/SQLite_database.sql @ e1c31cac

/* ---------------------------------------------------- */
/*  Generated by Enterprise Architect Version 13.5 		*/
/*  Created On : 29-dub-2021 11:19:55 				*/
/*  DBMS       : SQLite 								*/
/* ---------------------------------------------------- */

/* Drop Tables */

DROP TABLE IF EXISTS 'Certificates'
;

DROP TABLE IF EXISTS 'CertificateTypes'
;

DROP TABLE IF EXISTS 'CertificateUsages'
;

DROP TABLE IF EXISTS 'PrivateKeys'
;

DROP TABLE IF EXISTS 'Subjects'
;

DROP TABLE IF EXISTS 'UsageTypes'
;

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

CREATE TABLE 'Certificates'
(
	'id' INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
	'valid_from' TEXT NOT NULL,
	'valid_to' TEXT NOT NULL,
	'pem_data' TEXT NOT NULL,
	'revocation_date' TEXT NULL,
	'deletion_date' TEXT NULL,
	'revocation_reason' TEXT NULL,
	'certificate_type_id' INTEGER NOT NULL,
	'parent_certificate_id' INTEGER NOT NULL,
	'subject_id' INTEGER NOT NULL,
	'private_key_id' INTEGER NOT NULL,
	CONSTRAINT 'FK_Certificates' FOREIGN KEY ('parent_certificate_id') REFERENCES 'Certificates' ('id') ON DELETE No Action ON UPDATE No Action,
	CONSTRAINT 'FK_CertificateTypes' FOREIGN KEY ('certificate_type_id') REFERENCES 'CertificateTypes' ('id') ON DELETE No Action ON UPDATE No Action,
	CONSTRAINT 'FK_PrivateKeys' FOREIGN KEY ('private_key_id') REFERENCES 'PrivateKeys' ('id') ON DELETE No Action ON UPDATE No Action,
	CONSTRAINT 'FK_Subjects' FOREIGN KEY ('subject_id') REFERENCES 'Subjects' ('id') ON DELETE No Action ON UPDATE No Action
)
;

CREATE TABLE 'CertificateTypes'
(
	'id' INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
	'certificate_type' TEXT NOT NULL
)
;

CREATE TABLE 'CertificateUsages'
(
	'id' INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
	'certificate_id' INTEGER NOT NULL,
	'usage_type_id' INTEGER NOT NULL,
	CONSTRAINT 'FK_Certificates' FOREIGN KEY ('certificate_id') REFERENCES 'Certificates' ('id') ON DELETE Cascade ON UPDATE No Action,
	CONSTRAINT 'FK_UsageTypes' FOREIGN KEY ('usage_type_id') REFERENCES 'UsageTypes' ('id') ON DELETE No Action ON UPDATE No Action
)
;

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

CREATE TABLE 'Subjects'
(
	'id' INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
	'common_name' TEXT NULL,
	'country_code' TEXT NULL,
	'locality' TEXT NULL,
	'province' TEXT NULL,
	'organization' TEXT NULL,
	'organizational_unit' TEXT NULL,
	'email_address' TEXT NULL
)
;

CREATE TABLE 'UsageTypes'
(
	'id' INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
	'usage_type' TEXT NOT NULL
)
;