diff options
Diffstat (limited to 'src/hydrilla/proxy/tables.sql')
| -rw-r--r-- | src/hydrilla/proxy/tables.sql | 18 |
1 files changed, 17 insertions, 1 deletions
diff --git a/src/hydrilla/proxy/tables.sql b/src/hydrilla/proxy/tables.sql index 0c604fc..3b3506d 100644 --- a/src/hydrilla/proxy/tables.sql +++ b/src/hydrilla/proxy/tables.sql @@ -167,9 +167,12 @@ CREATE TABLE mapping_statuses( FOREIGN KEY (item_id) REFERENCES items (item_id) ON DELETE CASCADE, + -- We'd like to set "active_version_id" to NULL when referenced entry is + -- deleted, but we cannot do it with ON DELETE clause because the + -- foreign key is composite. For now - this will be done by the + -- application. FOREIGN KEY (active_version_id, item_id) REFERENCES item_versions (item_version_id, item_id) - ON DELETE SET NULL ); CREATE TABLE item_versions( @@ -177,12 +180,19 @@ CREATE TABLE item_versions( item_id INTEGER NOT NULL, version VARCHAR NOT NULL, + -- "installed" determines whether item is INSTALLED, is NOT_INSTALLED or + -- it FAILED_TO_INSTALL when last tried. If "required" in a row of + -- "mapping_statuses is set to TRUE, the mapping version and all + -- resource versions corresponding to it are supposed to have + -- "installed" set to 'I'. + installed CHAR(1) NOT NULL, repo_iteration_id INTEGER NOT NULL, definition BLOB NOT NULL, UNIQUE (item_id, version, repo_iteration_id), -- Constraint below needed to allow foreign key from "mapping_statuses". UNIQUE (item_version_id, item_id), + CHECK (installed in ('I', 'N', 'F')), FOREIGN KEY (item_id) REFERENCES items (item_id), @@ -281,7 +291,10 @@ CREATE TABLE resolved_depended_resources( CREATE TABLE files( file_id INTEGER PRIMARY KEY, + -- File's hash as hexadecimal string. sha256 CHAR(64) NOT NULL, + -- The value of "data" - if not NULL - shall be a bytes sequence that + -- corresponds the hash stored in "sha256". data BLOB NULL, UNIQUE (sha256) @@ -290,6 +303,9 @@ CREATE TABLE files( CREATE TABLE file_uses( file_use_id INTEGER PRIMARY KEY, + -- If item version referenced by "item_version_id" has "installed" set + -- to 'I', the file referenced by "file_id" is supposed to have "data" + -- set to a valid, non-NULL value. item_version_id INTEGER NOT NULL, file_id INTEGER NOT NULL, name VARCHAR NOT NULL, |