Revize 61858091

Delete init_queries.py

src/db/init_queries.py

@@ -1 @@
-SCHEMA_SQL = """
-/* ---------------------------------------------------- */
-/*  Generated by Enterprise Architect Version 13.5 		*/
-/*  Created On : 29-dub-2021 11:55:46 				    */
-/*  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 '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,
-	'common_name' TEXT NOT NULL,
-	'country_code' TEXT NULL,
-	'locality' TEXT NULL,
-	'province' TEXT NULL,
-	'organization' TEXT NULL,
-	'organizational_unit' TEXT NULL,
-	'email_address' TEXT NULL,
-	'revocation_date' TEXT NULL,
-	'revocation_reason' TEXT NULL,
-	'deletion_date' TEXT NULL,
-	'certificate_type_id' INTEGER NOT NULL,
-	'parent_certificate_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
-)
-;
-
-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 'UsageTypes'
-(
-	'id' INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
-	'usage_type' TEXT NOT NULL
-)
-;
-
-"""
-
-# TODO static tables are dropped
-CHECK_SCHEMA_SQL = """
-/* Drop Tables */
-DROP TABLE IF EXISTS 'CertificateTypes'
-;
-
-DROP TABLE IF EXISTS 'CertificateUsages'
-;
-
-/* Create Tables with Primary and Foreign Keys, Check and Unique Constraints */
-
-CREATE TABLE IF NOT EXISTS 'Certificates'
-(
-	'id' INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
-	'valid_from' INTEGER NOT NULL,
-	'valid_to' INTEGER NOT NULL,
-	'pem_data' TEXT NOT NULL,
-	'common_name' TEXT NOT NULL,
-	'country_code' TEXT NULL,
-	'locality' TEXT NULL,
-	'province' TEXT NULL,
-	'organization' TEXT NULL,
-	'organizational_unit' TEXT NULL,
-	'email_address' TEXT NULL,
-	'revocation_date' INTEGER NULL,
-	'revocation_reason' TEXT NULL,
-	'deletion_date' INTEGER NULL,
-	'certificate_type_id' INTEGER NOT NULL,
-	'parent_certificate_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
-)
-;
-
-CREATE TABLE IF NOT EXISTS 'CertificateTypes'
-(
-	'id' INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
-	'certificate_type' TEXT NOT NULL
-)
-;
-
-CREATE TABLE IF NOT EXISTS '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 IF NOT EXISTS 'PrivateKeys'
-(
-	'id' INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
-	'private_key' TEXT NOT NULL,
-	'password' TEXT NULL
-)
-;
-
-CREATE TABLE IF NOT EXISTS 'UsageTypes'
-(
-	'id' INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
-	'usage_type' TEXT NOT NULL
-)
-;
-
-"""
-
-DEFAULT_VALUES_SQL = """
-/* Insert default values */
-
-INSERT INTO CertificateTypes(certificate_type) VALUES('ROOT_CA');
-INSERT INTO CertificateTypes(certificate_type) VALUES('INTERMEDIATE_CA');
-INSERT INTO CertificateTypes(certificate_type) VALUES('CERTIFICATE');
-
-INSERT INTO UsageTypes(usage_type) VALUES('CA');
-INSERT INTO UsageTypes(usage_type) VALUES('SSL');
-INSERT INTO UsageTypes(usage_type) VALUES('SIGNATURE');
-INSERT INTO UsageTypes(usage_type) VALUES('AUTHENTICATION');
-
-"""