diff options
Diffstat (limited to 'src/hydrilla/proxy/tables.sql')
| -rw-r--r-- | src/hydrilla/proxy/tables.sql | 31 |
1 files changed, 14 insertions, 17 deletions
diff --git a/src/hydrilla/proxy/tables.sql b/src/hydrilla/proxy/tables.sql index 0417613..a915f74 100644 --- a/src/hydrilla/proxy/tables.sql +++ b/src/hydrilla/proxy/tables.sql @@ -155,6 +155,20 @@ CREATE TABLE item_versions( REFERENCES repo_iterations (repo_iteration_id) ); +CREATE VIEW repo_display_infos +AS +SELECT + r.repo_id, r.name, r.url, r.deleted, r.last_refreshed, + COALESCE(SUM(i.type = 'R'), 0) AS resource_count, + COALESCE(SUM(i.type = 'M'), 0) AS mapping_count +FROM + repos AS r + LEFT JOIN repo_iterations AS ir USING (repo_id) + LEFT JOIN item_versions AS iv USING (repo_iteration_id) + LEFT JOIN items AS i USING (item_id) +GROUP BY + r.repo_id, r.name, r.url, r.deleted, r.last_refreshed; + CREATE TABLE payloads( payload_id INTEGER PRIMARY KEY, @@ -225,23 +239,6 @@ CREATE TABLE resolved_depended_resources( ON DELETE CASCADE ) WITHOUT ROWID; --- CREATE TABLE resolved_required_mappings( --- requiring_item_id INTEGER, --- required_mapping_item_id INTEGER, - --- PRIMARY KEY (requiring_item_id, required_mapping_item_id), - --- CHECK (requiring_item_id != required_mapping_item_id), - --- FOREIGN KEY (requiring_item_id) --- REFERENCES items (item_id), --- -- Note: the referenced mapping shall have installed=TRUE. --- FOREIGN KEY (required_mapping_item_id) --- REFERENCES mappings (item_id), --- FOREIGN KEY (required_mapping_item_id) --- REFERENCES items (item_id) --- ); - CREATE TABLE files( file_id INTEGER PRIMARY KEY, |