diff options
| -rw-r--r-- | gentoobrowse/console/packagemasks.xml | 43 | ||||
| -rw-r--r-- | gentoobrowse/datasources/schema.sql | 86 |
2 files changed, 47 insertions, 82 deletions
diff --git a/gentoobrowse/console/packagemasks.xml b/gentoobrowse/console/packagemasks.xml index 6fafbb4..2e5e2e3 100644 --- a/gentoobrowse/console/packagemasks.xml +++ b/gentoobrowse/console/packagemasks.xml @@ -1,41 +1,40 @@ <?xml version="1.0"?> <packagemasks name="packagemasks" xmlns:xi="http://www.w3.org/2001/XInclude" xmlns:project2="http://project2.randomdan.homeip.net"> + <project2:sqltask name="tmpmasks" datasource="postgres"> + <sql> + CREATE TEMPORARY TABLE tmpmasks AS + SELECT to_date(m[3], 'dd mon yyyy') as dateadded, RANK() OVER(PARTITION BY to_date(m[3], 'dd mon yyyy') ORDER BY n DESC) n, + TRIM(m[1]) as person, m[2] as email, m[4] as message, m[5] as atoms + FROM ( + SELECT row_number() over() n, + regexp_matches(r, '^# ([^<]+)? ?<(.+?@[^>]+)> \((\d+ *(?:jan|feb|mar|apr|may|jun|jul|aug|sep|oct|nov|dec) \d+)\)\n((?:#[^\n]*\n)+)(.*)$', 'i') m + FROM files f, REGEXP_SPLIT_TO_TABLE(PG_READ_FILE(filename), '\n\s*\n') r + WHERE filetypeid = 3) m + </sql> + </project2:sqltask> <project2:sqlmerge name="masksets" datasource="postgres" targettable="masksets" useview="yes"> <sql> - SELECT d.setno, - MIN(field[1]) AS person, - MIN(field[2]) AS email, - CAST((REGEXP_MATCHES(MIN(field[3]), E'(\\d+ *(jan|feb|mar|apr|may|jun|jul|aug|sep|oct|nov|dec)\\w* \\d+)', 'i'))[1] AS DATE) AS dateadded, - TRIM(STRING_AGG(REGEXP_REPLACE(content, '^#+', ''), ' ' ORDER BY m.line)) AS message - FROM (SELECT setno, line AS line, REGEXP_MATCHES(content, E'^# *(.+) +< *(.+) *> \\( *(.+) *\\)$') AS field - FROM file_masks_withset - WHERE line IN(SELECT min(line) - FROM file_masks_withset - GROUP BY setno)) AS d, - (SELECT c.line, c.content, c.setno - FROM file_masks_withset c - WHERE c.content LIKE '#%') AS m - WHERE m.setno = d.setno - AND m.line > d.line - GROUP BY d.setno + SELECT dateadded, n, person, email, + TRIM(REGEXP_REPLACE(message, '#?\s+', ' ', 'g')) message + FROM tmpmasks </sql> <columns> - <setno key="true" /> + <dateadded key="true" /> + <n key="true" /> <person /> <email /> - <dateadded /> <message /> </columns> </project2:sqlmerge> <project2:sqlmerge name="masks" datasource="postgres" targettable="ebuild_masks" useview="yes"> <sql> - SELECT line, setno, ebuildfilter(content) AS ebuildid - FROM file_masks_withset - WHERE content NOT LIKE '#%' + SELECT s.setno, ebuildfilter(REGEXP_SPLIT_TO_TABLE(tm.atoms, '\n')) ebuildid + FROM tmpmasks tm, masksets s + WHERE tm.dateadded = s.dateadded + AND tm.n = s.n </sql> <columns> - <line key="true" /> <setno key="true" /> <ebuildid key="true" /> </columns> 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; |