diff options
Diffstat (limited to 'gentoobrowse-api/service/sql/maintenance')
22 files changed, 137 insertions, 0 deletions
diff --git a/gentoobrowse-api/service/sql/maintenance/bugAssociate.sql b/gentoobrowse-api/service/sql/maintenance/bugAssociate.sql new file mode 100644 index 0000000..e51d4d9 --- /dev/null +++ b/gentoobrowse-api/service/sql/maintenance/bugAssociate.sql @@ -0,0 +1,4 @@ +SELECT b.bugid, p.packageid +FROM gentoobrowse.bugs b, gentoobrowse.packages p, gentoobrowse.categories c +WHERE p.categoryid = c.categoryid +AND gentoobrowse.namedpackages(b.summary) @> ARRAY[c.name || '/' || p.name] diff --git a/gentoobrowse-api/service/sql/maintenance/categoryInsert.sql b/gentoobrowse-api/service/sql/maintenance/categoryInsert.sql new file mode 100644 index 0000000..27321a0 --- /dev/null +++ b/gentoobrowse-api/service/sql/maintenance/categoryInsert.sql @@ -0,0 +1,3 @@ +INSERT INTO gentoobrowse.categories(name) +SELECT ? +WHERE (NOT EXISTS (SELECT name FROM gentoobrowse.categories WHERE name = ?)) diff --git a/gentoobrowse-api/service/sql/maintenance/categoryMetaUpdate.sql b/gentoobrowse-api/service/sql/maintenance/categoryMetaUpdate.sql new file mode 100644 index 0000000..28c45b1 --- /dev/null +++ b/gentoobrowse-api/service/sql/maintenance/categoryMetaUpdate.sql @@ -0,0 +1,3 @@ +UPDATE gentoobrowse.categories SET + summary = gentoobrowse.textclean(?) +WHERE name = ? diff --git a/gentoobrowse-api/service/sql/maintenance/categoryPrune.sql b/gentoobrowse-api/service/sql/maintenance/categoryPrune.sql new file mode 100644 index 0000000..3b75683 --- /dev/null +++ b/gentoobrowse-api/service/sql/maintenance/categoryPrune.sql @@ -0,0 +1,5 @@ +DELETE FROM gentoobrowse.categories c +WHERE NOT EXISTS ( + SELECT FROM gentoobrowse.packages p + WHERE p.categoryid = c.categoryid) + diff --git a/gentoobrowse-api/service/sql/maintenance/ebuildDelete.sql b/gentoobrowse-api/service/sql/maintenance/ebuildDelete.sql new file mode 100644 index 0000000..bd46bcd --- /dev/null +++ b/gentoobrowse-api/service/sql/maintenance/ebuildDelete.sql @@ -0,0 +1,9 @@ +DELETE FROM gentoobrowse.ebuilds e +USING gentoobrowse.packages p, gentoobrowse.categories c, gentoobrowse.repos r +WHERE e.packageid = p.packageid +AND c.categoryid = p.categoryid +AND e.repoid = r.repoid +AND r.name = ? +AND c.name = ? +AND p.name = ? +AND e.version = ? diff --git a/gentoobrowse-api/service/sql/maintenance/ebuildDeps.sql b/gentoobrowse-api/service/sql/maintenance/ebuildDeps.sql new file mode 100644 index 0000000..ae33b65 --- /dev/null +++ b/gentoobrowse-api/service/sql/maintenance/ebuildDeps.sql @@ -0,0 +1,8 @@ +( + SELECT DISTINCT ebuildid, packageid, e.version versionspec, flags, slot, op + FROM gentoobrowse.categories c, gentoobrowse.packages p, tmpEbuildDeps e + WHERE c.categoryid = p.categoryid + AND c.name = e.category + AND p.name = e.package + AND e.runtime = false +) diff --git a/gentoobrowse-api/service/sql/maintenance/ebuildInsert.sql b/gentoobrowse-api/service/sql/maintenance/ebuildInsert.sql new file mode 100644 index 0000000..2a34c36 --- /dev/null +++ b/gentoobrowse-api/service/sql/maintenance/ebuildInsert.sql @@ -0,0 +1,13 @@ +-- libdbpp:no-cursor +INSERT INTO gentoobrowse.ebuilds(packageid, version, versioninst, slot, license, moddate, repoid) +SELECT p.packageid, ?, gentoobrowse.ebuildversion_constructor(?), ?, ?, ?, r.repoid +FROM gentoobrowse.packages p, gentoobrowse.categories c, gentoobrowse.repos r +WHERE c.categoryid = p.categoryid +AND r.name = ? +AND c.name = ? +AND p.name = ? +RETURNING ebuildid, NOT EXISTS ( + SELECT FROM gentoobrowse.ebuilds ve + WHERE ve.packageid = gentoobrowse.ebuilds.packageid + AND ve.versioninst > gentoobrowse.ebuilds.versioninst + ), packageid diff --git a/gentoobrowse-api/service/sql/maintenance/ebuildRDeps.sql b/gentoobrowse-api/service/sql/maintenance/ebuildRDeps.sql new file mode 100644 index 0000000..b0e54b9 --- /dev/null +++ b/gentoobrowse-api/service/sql/maintenance/ebuildRDeps.sql @@ -0,0 +1,8 @@ +( + SELECT DISTINCT ebuildid, packageid, e.version versionspec, flags, slot, op + FROM gentoobrowse.categories c, gentoobrowse.packages p, tmpEbuildDeps e + WHERE c.categoryid = p.categoryid + AND c.name = e.category + AND p.name = e.package + AND e.runtime = true +) diff --git a/gentoobrowse-api/service/sql/maintenance/ebuildUpdate.sql b/gentoobrowse-api/service/sql/maintenance/ebuildUpdate.sql new file mode 100644 index 0000000..f5a42c5 --- /dev/null +++ b/gentoobrowse-api/service/sql/maintenance/ebuildUpdate.sql @@ -0,0 +1,17 @@ +-- libdbpp:no-cursor +UPDATE gentoobrowse.ebuilds e SET + slot = ?, + license = ?, + moddate = ? +FROM gentoobrowse.packages p, gentoobrowse.categories c, gentoobrowse.repos r +WHERE c.categoryid = p.categoryid +AND e.packageid = p.packageid +AND r.name = ? +AND c.name = ? +AND p.name = ? +AND e.version = ? +RETURNING ebuildid, NOT EXISTS ( + SELECT FROM gentoobrowse.ebuilds ve + WHERE ve.packageid = e.packageid + AND ve.versioninst > e.versioninst + ), e.packageid diff --git a/gentoobrowse-api/service/sql/maintenance/fileListCreate.sql b/gentoobrowse-api/service/sql/maintenance/fileListCreate.sql new file mode 100644 index 0000000..d8d627c --- /dev/null +++ b/gentoobrowse-api/service/sql/maintenance/fileListCreate.sql @@ -0,0 +1,12 @@ +CREATE TEMPORARY TABLE filelist AS +SELECT + r.repoid, + fl.filename, + ft.filetypeid, + ft.processOrder, + (STRING_TO_ARRAY(fl.filename, '/'))[2:100] pathparts, + fl.filesize, + fl.filemtime moddate +FROM filelistraw fl, gentoobrowse.repos r, gentoobrowse.filetypes ft +WHERE (STRING_TO_ARRAY(fl.filename, '/'))[1] = r.name +AND (STRING_TO_ARRAY(fl.filename, '/'))[2:100] ~ ft.spec diff --git a/gentoobrowse-api/service/sql/maintenance/fileListCreatePk.sql b/gentoobrowse-api/service/sql/maintenance/fileListCreatePk.sql new file mode 100644 index 0000000..1e56d8b --- /dev/null +++ b/gentoobrowse-api/service/sql/maintenance/fileListCreatePk.sql @@ -0,0 +1 @@ +ALTER TABLE filelist ADD CONSTRAINT pk_filelist PRIMARY KEY(filename) diff --git a/gentoobrowse-api/service/sql/maintenance/fileListCreateRaw.sql b/gentoobrowse-api/service/sql/maintenance/fileListCreateRaw.sql new file mode 100644 index 0000000..0fa576a --- /dev/null +++ b/gentoobrowse-api/service/sql/maintenance/fileListCreateRaw.sql @@ -0,0 +1,4 @@ +CREATE TEMPORARY TABLE filelistraw ( + filename TEXT NOT NULL, + filesize INTEGER NOT NULL, + filemtime TIMESTAMP WITHOUT TIME ZONE NOT NULL) diff --git a/gentoobrowse-api/service/sql/maintenance/masksEbuilds.sql b/gentoobrowse-api/service/sql/maintenance/masksEbuilds.sql new file mode 100644 index 0000000..f34343e --- /dev/null +++ b/gentoobrowse-api/service/sql/maintenance/masksEbuilds.sql @@ -0,0 +1,4 @@ +( + SELECT DISTINCT s.setno, ebuildfilter(UNNEST(s.atomSpec)) ebuildid + FROM gentoobrowse.masksets s +) diff --git a/gentoobrowse-api/service/sql/maintenance/masksSets.sql b/gentoobrowse-api/service/sql/maintenance/masksSets.sql new file mode 100644 index 0000000..873d60e --- /dev/null +++ b/gentoobrowse-api/service/sql/maintenance/masksSets.sql @@ -0,0 +1,5 @@ +( + SELECT tm.dateAdded, RANK() OVER(PARTITION BY tm.dateAdded ORDER BY n DESC) n, + tm.person, tm.email, tm.message, tm.atomSpec + FROM tmp_masks tm +) diff --git a/gentoobrowse-api/service/sql/maintenance/packageDescUpdate.sql b/gentoobrowse-api/service/sql/maintenance/packageDescUpdate.sql new file mode 100644 index 0000000..d3fed1c --- /dev/null +++ b/gentoobrowse-api/service/sql/maintenance/packageDescUpdate.sql @@ -0,0 +1,3 @@ +UPDATE gentoobrowse.packages p SET + description = ? +WHERE p.packageid = ? diff --git a/gentoobrowse-api/service/sql/maintenance/packageInsert.sql b/gentoobrowse-api/service/sql/maintenance/packageInsert.sql new file mode 100644 index 0000000..8e910f8 --- /dev/null +++ b/gentoobrowse-api/service/sql/maintenance/packageInsert.sql @@ -0,0 +1,7 @@ +INSERT INTO gentoobrowse.packages(categoryid, name, description) +SELECT c.categoryid, ?, ? +FROM gentoobrowse.categories c + LEFT OUTER JOIN gentoobrowse.packages p + ON c.categoryid = p.categoryid AND p.name = ? +WHERE c.name = ? +AND p.packageid IS NULL diff --git a/gentoobrowse-api/service/sql/maintenance/packageMetaUpdate.sql b/gentoobrowse-api/service/sql/maintenance/packageMetaUpdate.sql new file mode 100644 index 0000000..c065d3f --- /dev/null +++ b/gentoobrowse-api/service/sql/maintenance/packageMetaUpdate.sql @@ -0,0 +1,7 @@ +UPDATE gentoobrowse.packages p SET + summary = gentoobrowse.textclean(?), + herd = ? +FROM gentoobrowse.categories c +WHERE p.categoryid = c.categoryid +AND c.name = ? +AND p.name = ? diff --git a/gentoobrowse-api/service/sql/maintenance/packagePrune.sql b/gentoobrowse-api/service/sql/maintenance/packagePrune.sql new file mode 100644 index 0000000..510ed75 --- /dev/null +++ b/gentoobrowse-api/service/sql/maintenance/packagePrune.sql @@ -0,0 +1,7 @@ +DELETE FROM gentoobrowse.packages p +USING gentoobrowse.categories c +WHERE p.categoryid = c.categoryid +AND c.name = ? +AND NOT EXISTS ( + SELECT FROM gentoobrowse.ebuilds e + WHERE p.packageid = e.packageid) diff --git a/gentoobrowse-api/service/sql/maintenance/useGroupsDelete.sql b/gentoobrowse-api/service/sql/maintenance/useGroupsDelete.sql new file mode 100644 index 0000000..7fbac23 --- /dev/null +++ b/gentoobrowse-api/service/sql/maintenance/useGroupsDelete.sql @@ -0,0 +1,2 @@ +DELETE FROM gentoobrowse.use_groups +WHERE name = ? diff --git a/gentoobrowse-api/service/sql/maintenance/useGroupsGetId.sql b/gentoobrowse-api/service/sql/maintenance/useGroupsGetId.sql new file mode 100644 index 0000000..56d05c2 --- /dev/null +++ b/gentoobrowse-api/service/sql/maintenance/useGroupsGetId.sql @@ -0,0 +1,4 @@ +-- libdbpp:no-cursor +SELECT usegroupid +FROM gentoobrowse.use_groups +WHERE name = ? diff --git a/gentoobrowse-api/service/sql/maintenance/useGroupsInsert.sql b/gentoobrowse-api/service/sql/maintenance/useGroupsInsert.sql new file mode 100644 index 0000000..1a99c8a --- /dev/null +++ b/gentoobrowse-api/service/sql/maintenance/useGroupsInsert.sql @@ -0,0 +1,4 @@ +-- libdbpp:no-cursor +INSERT INTO gentoobrowse.use_groups(name) +VALUES(?) +RETURNING usegroupid diff --git a/gentoobrowse-api/service/sql/maintenance/useLocalMap.sql b/gentoobrowse-api/service/sql/maintenance/useLocalMap.sql new file mode 100644 index 0000000..2d12184 --- /dev/null +++ b/gentoobrowse-api/service/sql/maintenance/useLocalMap.sql @@ -0,0 +1,7 @@ +( + SELECT p.packageId, d.use, d.description + FROM gentoobrowse.categories c, gentoobrowse.packages p, tmp_uselocalraw d + WHERE c.categoryId = p.categoryId + AND c.name = d.category + AND p.name = d.package +) |