Index: Application/Ringtoets/src/Application.Ringtoets.Storage/DbContext/DatabaseStructure.sql =================================================================== diff -u -r8f13852513ec66f8725df3da17aa0c1895b04382 -radfa3c3e4d889f4038d1add832f3ea16aaeaadac --- Application/Ringtoets/src/Application.Ringtoets.Storage/DbContext/DatabaseStructure.sql (.../DatabaseStructure.sql) (revision 8f13852513ec66f8725df3da17aa0c1895b04382) +++ Application/Ringtoets/src/Application.Ringtoets.Storage/DbContext/DatabaseStructure.sql (.../DatabaseStructure.sql) (revision adfa3c3e4d889f4038d1add832f3ea16aaeaadac) @@ -1,51 +1,59 @@ /* ---------------------------------------------------- */ /* Generated by Enterprise Architect Version 12.0 */ -/* Created On : 17-Mar-2016 11:55:25 */ +/* Created On : 17-Mar-2016 1:37:07 PM */ /* DBMS : SQLite */ /* ---------------------------------------------------- */ /* Drop Tables */ -DROP TABLE IF EXISTS 'DikeAssessmentSectionEntity' +DROP TABLE IF EXISTS 'Version' ; -DROP TABLE IF EXISTS 'FailureMechanismEntity' +DROP TABLE IF EXISTS 'ReferenceLinePointEntity' ; +DROP TABLE IF EXISTS 'ProjectEntity' +; + DROP TABLE IF EXISTS 'HydraulicLocationEntity' ; -DROP TABLE IF EXISTS 'ProjectEntity' +DROP TABLE IF EXISTS 'FailureMechanismEntity' ; -DROP TABLE IF EXISTS 'Version' +DROP TABLE IF EXISTS 'DikeAssessmentSectionEntity' ; /* Create Tables with Primary and Foreign Keys, Check and Unique Constraints */ -CREATE TABLE 'DikeAssessmentSectionEntity' +CREATE TABLE 'Version' ( - 'DikeAssessmentSectionEntityId' INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, - 'ProjectEntityId' INTEGER NOT NULL, - 'Name' VARCHAR NOT NULL, - 'Norm' INT(4) NOT NULL, - 'Order' INT(4) NOT NULL, - 'HydraulicDatabaseVersion' TEXT, - 'HydraulicDatabaseLocation' TEXT, - CONSTRAINT 'FK_AssessmentSection_ProjectEntity' FOREIGN KEY ('ProjectEntityId') REFERENCES 'ProjectEntity' ('ProjectEntityId') ON DELETE Cascade ON UPDATE Cascade + 'VersionId' INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, + 'FromVersion' TEXT, + 'ToVersion' TEXT, + 'Timestamp' NUMERIC ) ; -CREATE TABLE 'FailureMechanismEntity' +CREATE TABLE 'ReferenceLinePointEntity' ( - 'FailureMechanismEntityId' INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, - 'DikeAssessmentSectionEntityId' INTEGER NOT NULL, - 'FailureMechanismType' SMALLINT NOT NULL, -- Enumerator for different failure mechanism types (piping, macrostability, dunes, etc) - CONSTRAINT 'FK_FailureMechanismEntity_AssessmentSectionEntity' FOREIGN KEY ('DikeAssessmentSectionEntityId') REFERENCES 'DikeAssessmentSectionEntity' ('DikeAssessmentSectionEntityId') ON DELETE Cascade ON UPDATE Cascade, - CONSTRAINT 'UI_AS_FMT' UNIQUE ('FailureMechanismType','DikeAssessmentSectionEntityId') + 'ReferenceLinePointEntityId' INTEGER NOT NULL PRIMARY KEY, + 'DikeAssessmentSectionEntityId' INTEGER, + 'X' NUMERIC, + 'Y' NUMERIC, + 'Order' INTEGER, + CONSTRAINT 'FK_ReferenceLinePointEntity_DikeAssessmentSectionEntity' FOREIGN KEY ('DikeAssessmentSectionEntityId') REFERENCES 'DikeAssessmentSectionEntity' ('DikeAssessmentSectionEntityId') ON DELETE Cascade ON UPDATE Cascade ) ; +CREATE TABLE 'ProjectEntity' +( + 'ProjectEntityId' INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, + 'Description' TEXT, + 'LastUpdated' NUMERIC DEFAULT CURRENT_TIMESTAMP +) +; + CREATE TABLE 'HydraulicLocationEntity' ( 'HydraulicLocationEntityId' INTEGER NOT NULL PRIMARY KEY, @@ -58,33 +66,43 @@ ) ; -CREATE TABLE 'ProjectEntity' +CREATE TABLE 'FailureMechanismEntity' ( - 'ProjectEntityId' INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, - 'Description' TEXT, - 'LastUpdated' NUMERIC DEFAULT CURRENT_TIMESTAMP + 'FailureMechanismEntityId' INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, + 'DikeAssessmentSectionEntityId' INTEGER NOT NULL, + 'FailureMechanismType' SMALLINT NOT NULL, -- Enumerator for different failure mechanism types (piping, macrostability, dunes, etc) + CONSTRAINT 'FK_FailureMechanismEntity_AssessmentSectionEntity' FOREIGN KEY ('DikeAssessmentSectionEntityId') REFERENCES 'DikeAssessmentSectionEntity' ('DikeAssessmentSectionEntityId') ON DELETE Cascade ON UPDATE Cascade, + CONSTRAINT 'UI_AS_FMT' UNIQUE ('FailureMechanismType','DikeAssessmentSectionEntityId') ) ; -CREATE TABLE 'Version' +CREATE TABLE 'DikeAssessmentSectionEntity' ( - 'VersionId' INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, - 'FromVersion' TEXT, - 'ToVersion' TEXT, - 'Timestamp' NUMERIC + 'DikeAssessmentSectionEntityId' INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, + 'ProjectEntityId' INTEGER NOT NULL, + 'Name' VARCHAR NOT NULL, + 'Norm' INT(4) NOT NULL, + 'Order' INT(4) NOT NULL, + 'HydraulicDatabaseVersion' TEXT, + 'HydraulicDatabaseLocation' TEXT, + CONSTRAINT 'FK_AssessmentSection_ProjectEntity' FOREIGN KEY ('ProjectEntityId') REFERENCES 'ProjectEntity' ('ProjectEntityId') ON DELETE Cascade ON UPDATE Cascade ) ; /* Create Indexes and Triggers */ -CREATE INDEX 'IXFK_AssessmentSection_ProjectEntity' - ON 'DikeAssessmentSectionEntity' ('ProjectEntityId' ASC) +CREATE INDEX 'IXFK_ReferenceLinePointEntity_DikeAssessmentSectionEntity' + ON 'ReferenceLinePointEntity' ('DikeAssessmentSectionEntityId' ASC) ; +CREATE INDEX 'IXFK_HydraulicLocationEntity_DikeAssessmentSectionEntity' + ON 'HydraulicLocationEntity' ('DikeAssessmentSectionEntityId' ASC) +; + CREATE INDEX 'IXFK_FailureMechanismEntity_AssessmentSectionEntity' ON 'FailureMechanismEntity' ('DikeAssessmentSectionEntityId' ASC) ; -CREATE INDEX 'IXFK_HydraulicLocationEntity_DikeAssessmentSectionEntity' - ON 'HydraulicLocationEntity' ('DikeAssessmentSectionEntityId' ASC) +CREATE INDEX 'IXFK_AssessmentSection_ProjectEntity' + ON 'DikeAssessmentSectionEntity' ('ProjectEntityId' ASC) ;