-- SWH DB schema upgrade -- from_version: 76 -- to_version: 77 -- description: Add content provenance information cache tables insert into dbversion(version, release, description) values(77, now(), 'Work In Progress'); CREATE TABLE cache_content_revision ( content sha1_git NOT NULL, revision sha1_git NOT NULL, "path" unix_path NOT NULL ); CREATE TABLE cache_revision_origin ( revision sha1_git NOT NULL, origin bigint NOT NULL, visit bigint NOT NULL ); ALTER TABLE cache_content_revision ADD CONSTRAINT cache_content_revision_pkey PRIMARY KEY (content, revision, path); ALTER TABLE cache_revision_origin ADD CONSTRAINT cache_revision_origin_pkey PRIMARY KEY (revision, origin, visit); ALTER TABLE cache_content_revision ADD CONSTRAINT cache_content_revision_content_fkey FOREIGN KEY (content) REFERENCES content(sha1_git); ALTER TABLE cache_content_revision ADD CONSTRAINT cache_content_revision_revision_fkey FOREIGN KEY (revision) REFERENCES revision(id); ALTER TABLE cache_revision_origin ADD CONSTRAINT cache_revision_origin_origin_fkey FOREIGN KEY (origin, visit) REFERENCES origin_visit(origin, visit); ALTER TABLE cache_revision_origin ADD CONSTRAINT cache_revision_origin_revision_fkey FOREIGN KEY (revision) REFERENCES revision(id); CREATE INDEX cache_content_revision_content_idx ON cache_content_revision USING btree (content); CREATE INDEX cache_content_revision_revision_idx ON cache_content_revision USING btree (revision); CREATE INDEX cache_revision_origin_revision_idx ON cache_revision_origin USING btree (revision);