Revize 46867cf8

Re #8305 - Created ERA model and DB script
- ERA model
- script for generate DB
- script for insert default values into DB

SQLite_database.sql

@@ +1 @@
+/* ---------------------------------------------------- */
+/*  Generated by Enterprise Architect Version 13.5 		*/
+/*  Created On : 26-b?e-2021 13:33:05 				*/
+/*  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
+)
+;
+
+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,
+	'password' TEXT NULL,
+	'pem_data' TEXT NOT NULL,
+	'type_id' INTEGER NULL,
+	'usage_id' INTEGER NULL,
+	'private_key_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
+)
+;

SQLite_default_values.sql

@@ +1 @@
+/* ---------------------------------------------------- */
+/*  Generated by Enterprise Architect Version 13.5 		*/
+/*  Created On : 26-bře-2021 13:33:05 				*/
+/*  DBMS       : SQLite 								*/
+/* ---------------------------------------------------- */
+
+/* Insert default values */
+
+INSERT INTO CertificateType(certificate_type) VALUES('ROOT_CA');
+INSERT INTO CertificateType(certificate_type) VALUES('INTERMEDIATE_CA');
+INSERT INTO CertificateType(certificate_type) VALUES('CERTIFICATE');
+
+INSERT INTO CertificateUsage(certificate_usage) VALUES('CA');
+INSERT INTO CertificateUsage(certificate_usage) VALUES('SSL');
+INSERT INTO CertificateUsage(certificate_usage) VALUES('SIGNATURE');
+INSERT INTO CertificateUsage(certificate_usage) VALUES('AUTHENTICATION');