Replace package/ebuild masks, drop no longer required views and tables

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, '^# ([^&lt;]+)? ?&lt;(.+?@[^&gt;]+)&gt; \((\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'^# *(.+) +&lt; *(.+) *&gt; \\( *(.+) *\\)$') 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;