diff options
Diffstat (limited to 'gentoobrowse/datasources/schema.sql')
| -rw-r--r-- | gentoobrowse/datasources/schema.sql | 86 |
1 files changed, 26 insertions, 60 deletions
diff --git a/gentoobrowse/datasources/schema.sql b/gentoobrowse/datasources/schema.sql index d737ef7..1f1478f 100644 --- a/gentoobrowse/datasources/schema.sql +++ b/gentoobrowse/datasources/schema.sql @@ -284,7 +284,6 @@ ALTER TABLE ebuild_deps OWNER TO gentoo; COMMENT ON TABLE ebuild_deps IS 'The dependencies of ebuilds (includes blocks, etc)'; -- Name: ebuild_masks; Type: TABLE; Schema: gentoobrowse; Owner: gentoo; Tablespace: CREATE TABLE ebuild_masks ( - line integer NOT NULL, setno integer NOT NULL, ebuildid integer NOT NULL ); @@ -322,13 +321,6 @@ CREATE TABLE ebuilds ( ALTER TABLE ebuilds OWNER TO gentoo; -- Name: TABLE ebuilds; Type: COMMENT; Schema: gentoobrowse; Owner: gentoo COMMENT ON TABLE ebuilds IS 'Ebuilds :-)'; --- Name: filecache; Type: TABLE; Schema: gentoobrowse; Owner: gentoo; Tablespace: -CREATE TABLE filecache ( - line integer NOT NULL, - content text NOT NULL, - fileid integer NOT NULL -); -ALTER TABLE filecache OWNER TO gentoo; -- Name: files; Type: TABLE; Schema: gentoobrowse; Owner: gentoo; Tablespace: CREATE TABLE files ( filename text NOT NULL, @@ -342,28 +334,6 @@ CREATE TABLE files ( pathparts text[] ); ALTER TABLE files OWNER TO gentoo; --- Name: file_masks; Type: VIEW; Schema: gentoobrowse; Owner: gentoo -CREATE VIEW file_masks AS - SELECT fc.line, - fc.content - FROM files f, - filecache fc - WHERE (((f.filename = 'package.mask'::text) AND (f.fileid = fc.fileid)) AND (fc.line > 32)); -ALTER TABLE file_masks OWNER TO gentoo; --- Name: file_masks_withset; Type: VIEW; Schema: gentoobrowse; Owner: gentoo -CREATE VIEW file_masks_withset AS - SELECT o.line, - o.content, - CASE - WHEN (o.content !~~ '#%'::text) THEN ( SELECT max(i.line) AS max - FROM file_masks i - WHERE ((i.line <= o.line) AND (substr(i.content, 1, 1) = '#'::text))) - ELSE ( SELECT (min(i.line) - 1) - FROM file_masks i - WHERE ((i.line >= o.line) AND (substr(i.content, 1, 1) <> '#'::text))) - END AS setno - FROM file_masks o; -ALTER TABLE file_masks_withset OWNER TO gentoo; -- Name: filetypes; Type: TABLE; Schema: gentoobrowse; Owner: gentoo; Tablespace: CREATE TABLE filetypes ( filetypeid integer NOT NULL, @@ -382,12 +352,23 @@ ALTER TABLE license OWNER TO gentoo; -- Name: masksets; Type: TABLE; Schema: gentoobrowse; Owner: gentoo; Tablespace: CREATE TABLE masksets ( setno integer NOT NULL, - person text NOT NULL, + person text, email text NOT NULL, dateadded date NOT NULL, - message text NOT NULL + message text NOT NULL, + n smallint NOT NULL ); ALTER TABLE masksets OWNER TO gentoo; +-- Name: masksets_setno_seq; Type: SEQUENCE; Schema: gentoobrowse; Owner: gentoo +CREATE SEQUENCE masksets_setno_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; +ALTER TABLE masksets_setno_seq OWNER TO gentoo; +-- Name: masksets_setno_seq; Type: SEQUENCE OWNED BY; Schema: gentoobrowse; Owner: gentoo +ALTER SEQUENCE masksets_setno_seq OWNED BY masksets.setno; -- Name: package_bugs; Type: TABLE; Schema: gentoobrowse; Owner: gentoo; Tablespace: CREATE TABLE package_bugs ( packageid integer NOT NULL, @@ -540,6 +521,8 @@ ALTER SEQUENCE users_userid_seq OWNED BY users.userid; ALTER TABLE ONLY categories ALTER COLUMN categoryid SET DEFAULT nextval('seq_categoryid'::regclass); -- Name: fileid; Type: DEFAULT; Schema: gentoobrowse; Owner: gentoo ALTER TABLE ONLY files ALTER COLUMN fileid SET DEFAULT nextval('seq_fileid'::regclass); +-- Name: setno; Type: DEFAULT; Schema: gentoobrowse; Owner: gentoo +ALTER TABLE ONLY masksets ALTER COLUMN setno SET DEFAULT nextval('masksets_setno_seq'::regclass); -- Name: packageid; Type: DEFAULT; Schema: gentoobrowse; Owner: gentoo ALTER TABLE ONLY packages ALTER COLUMN packageid SET DEFAULT nextval('seq_packageid'::regclass); -- Name: repoid; Type: DEFAULT; Schema: gentoobrowse; Owner: gentoo @@ -565,6 +548,9 @@ ALTER TABLE ebuild_archs CLUSTER ON pk_ebuildarch; ALTER TABLE ONLY ebuild_deps ADD CONSTRAINT pk_ebuilddeps PRIMARY KEY (ebuildid, packageid, versionspec, flags, slot, op); ALTER TABLE ebuild_deps CLUSTER ON pk_ebuilddeps; +-- Name: pk_ebuildmasks; Type: CONSTRAINT; Schema: gentoobrowse; Owner: gentoo; Tablespace: +ALTER TABLE ONLY ebuild_masks + ADD CONSTRAINT pk_ebuildmasks PRIMARY KEY (ebuildid, setno); -- Name: pk_ebuildrdeps; Type: CONSTRAINT; Schema: gentoobrowse; Owner: gentoo; Tablespace: ALTER TABLE ONLY ebuild_rdeps ADD CONSTRAINT pk_ebuildrdeps PRIMARY KEY (ebuildid, packageid, versionspec, flags, slot, op); @@ -573,9 +559,6 @@ ALTER TABLE ebuild_rdeps CLUSTER ON pk_ebuildrdeps; ALTER TABLE ONLY ebuild_uses ADD CONSTRAINT pk_ebuilduses PRIMARY KEY (ebuildid, use); ALTER TABLE ebuild_uses CLUSTER ON pk_ebuilduses; --- Name: pk_filecache; Type: CONSTRAINT; Schema: gentoobrowse; Owner: gentoo; Tablespace: -ALTER TABLE ONLY filecache - ADD CONSTRAINT pk_filecache PRIMARY KEY (fileid, line); -- Name: pk_fileclass; Type: CONSTRAINT; Schema: gentoobrowse; Owner: gentoo; Tablespace: ALTER TABLE ONLY filetypes ADD CONSTRAINT pk_fileclass PRIMARY KEY (filetypeid); @@ -590,10 +573,6 @@ ALTER TABLE package_changelogs CLUSTER ON pk_package_changelogs; ALTER TABLE ONLY package_bugs ADD CONSTRAINT pk_packagebugs PRIMARY KEY (packageid, bugid); ALTER TABLE package_bugs CLUSTER ON pk_packagebugs; --- Name: pk_packagemasks; Type: CONSTRAINT; Schema: gentoobrowse; Owner: gentoo; Tablespace: -ALTER TABLE ONLY ebuild_masks - ADD CONSTRAINT pk_packagemasks PRIMARY KEY (ebuildid, setno, line); -ALTER TABLE ebuild_masks CLUSTER ON pk_packagemasks; -- Name: pk_packagemasksets; Type: CONSTRAINT; Schema: gentoobrowse; Owner: gentoo; Tablespace: ALTER TABLE ONLY masksets ADD CONSTRAINT pk_packagemasksets PRIMARY KEY (setno); @@ -667,9 +646,6 @@ CREATE INDEX idx_ebuildrdeps_package ON ebuild_rdeps USING btree (packageid); CREATE INDEX idx_ebuilds_firstseen ON ebuilds USING btree (firstseen); -- Name: idx_ebuilduses_use; Type: INDEX; Schema: gentoobrowse; Owner: gentoo; Tablespace: CREATE INDEX idx_ebuilduses_use ON ebuild_uses USING btree (use); --- Name: idx_filecache_line; Type: INDEX; Schema: gentoobrowse; Owner: gentoo; Tablespace: -CREATE INDEX idx_filecache_line ON filecache USING btree (line); -ALTER TABLE filecache CLUSTER ON idx_filecache_line; -- Name: idx_package_name; Type: INDEX; Schema: gentoobrowse; Owner: gentoo; Tablespace: CREATE INDEX idx_package_name ON packages USING btree (name); -- Name: idx_packagebugs_bug; Type: INDEX; Schema: gentoobrowse; Owner: gentoo; Tablespace: @@ -682,6 +658,8 @@ CREATE INDEX idx_packages_herd ON packages USING btree (herd); CREATE INDEX idx_uselocal_use ON use_local USING btree (use); -- Name: idx_userpackages_package; Type: INDEX; Schema: gentoobrowse; Owner: gentoo; Tablespace: CREATE INDEX idx_userpackages_package ON user_packages USING btree (packageid); +-- Name: uni_masksets_dateaddedn; Type: INDEX; Schema: gentoobrowse; Owner: gentoo; Tablespace: +CREATE INDEX uni_masksets_dateaddedn ON masksets USING btree (dateadded, n); -- Name: fk_ebuild_pkg; Type: FK CONSTRAINT; Schema: gentoobrowse; Owner: gentoo ALTER TABLE ONLY ebuilds ADD CONSTRAINT fk_ebuild_pkg FOREIGN KEY (packageid) REFERENCES packages(packageid) ON UPDATE CASCADE ON DELETE CASCADE; @@ -697,6 +675,12 @@ ALTER TABLE ONLY ebuild_deps -- Name: fk_ebuilddeps_pkg; Type: FK CONSTRAINT; Schema: gentoobrowse; Owner: gentoo ALTER TABLE ONLY ebuild_deps ADD CONSTRAINT fk_ebuilddeps_pkg FOREIGN KEY (packageid) REFERENCES packages(packageid) ON UPDATE CASCADE ON DELETE CASCADE; +-- Name: fk_ebuildmasks_ebuild; Type: FK CONSTRAINT; Schema: gentoobrowse; Owner: gentoo +ALTER TABLE ONLY ebuild_masks + ADD CONSTRAINT fk_ebuildmasks_ebuild FOREIGN KEY (ebuildid) REFERENCES ebuilds(ebuildid) ON UPDATE CASCADE ON DELETE CASCADE; +-- Name: fk_ebuildmasks_maskset; Type: FK CONSTRAINT; Schema: gentoobrowse; Owner: gentoo +ALTER TABLE ONLY ebuild_masks + ADD CONSTRAINT fk_ebuildmasks_maskset FOREIGN KEY (setno) REFERENCES masksets(setno) ON UPDATE CASCADE ON DELETE CASCADE; -- Name: fk_ebuildrdeps_ebuild; Type: FK CONSTRAINT; Schema: gentoobrowse; Owner: gentoo ALTER TABLE ONLY ebuild_rdeps ADD CONSTRAINT fk_ebuildrdeps_ebuild FOREIGN KEY (ebuildid) REFERENCES ebuilds(ebuildid) ON UPDATE CASCADE ON DELETE CASCADE; @@ -706,9 +690,6 @@ ALTER TABLE ONLY ebuild_rdeps -- Name: fk_ebuilduses_file; Type: FK CONSTRAINT; Schema: gentoobrowse; Owner: gentoo ALTER TABLE ONLY ebuild_uses ADD CONSTRAINT fk_ebuilduses_file FOREIGN KEY (ebuildid) REFERENCES ebuilds(ebuildid) ON UPDATE CASCADE ON DELETE CASCADE; --- Name: fk_filecache_fileid; Type: FK CONSTRAINT; Schema: gentoobrowse; Owner: gentoo -ALTER TABLE ONLY filecache - ADD CONSTRAINT fk_filecache_fileid FOREIGN KEY (fileid) REFERENCES files(fileid) ON DELETE CASCADE; -- Name: fk_files_type; Type: FK CONSTRAINT; Schema: gentoobrowse; Owner: gentoo ALTER TABLE ONLY files ADD CONSTRAINT fk_files_type FOREIGN KEY (filetypeid) REFERENCES filetypes(filetypeid) ON UPDATE CASCADE; @@ -803,26 +784,11 @@ REVOKE ALL ON TABLE ebuilds FROM PUBLIC; REVOKE ALL ON TABLE ebuilds FROM gentoo; GRANT ALL ON TABLE ebuilds TO gentoo; GRANT SELECT ON TABLE ebuilds TO apache; --- Name: filecache; Type: ACL; Schema: gentoobrowse; Owner: gentoo -REVOKE ALL ON TABLE filecache FROM PUBLIC; -REVOKE ALL ON TABLE filecache FROM gentoo; -GRANT ALL ON TABLE filecache TO gentoo; -GRANT SELECT ON TABLE filecache TO apache; -- Name: files; Type: ACL; Schema: gentoobrowse; Owner: gentoo REVOKE ALL ON TABLE files FROM PUBLIC; REVOKE ALL ON TABLE files FROM gentoo; GRANT ALL ON TABLE files TO gentoo; GRANT SELECT ON TABLE files TO apache; --- Name: file_masks; Type: ACL; Schema: gentoobrowse; Owner: gentoo -REVOKE ALL ON TABLE file_masks FROM PUBLIC; -REVOKE ALL ON TABLE file_masks FROM gentoo; -GRANT ALL ON TABLE file_masks TO gentoo; -GRANT SELECT ON TABLE file_masks TO apache; --- Name: file_masks_withset; Type: ACL; Schema: gentoobrowse; Owner: gentoo -REVOKE ALL ON TABLE file_masks_withset FROM PUBLIC; -REVOKE ALL ON TABLE file_masks_withset FROM gentoo; -GRANT ALL ON TABLE file_masks_withset TO gentoo; -GRANT SELECT ON TABLE file_masks_withset TO apache; -- Name: filetypes; Type: ACL; Schema: gentoobrowse; Owner: gentoo REVOKE ALL ON TABLE filetypes FROM PUBLIC; REVOKE ALL ON TABLE filetypes FROM gentoo; |