diff options
| -rw-r--r-- | gentoobrowse-api/api/portage.ice | 6 | ||||
| -rw-r--r-- | gentoobrowse-api/db/schema.sql | 2 | ||||
| -rw-r--r-- | gentoobrowse-api/service/portageimpl.cpp | 12 | ||||
| -rw-r--r-- | gentoobrowse-api/service/portageimpl.h | 6 | ||||
| -rw-r--r-- | gentoobrowse-api/service/sql/portage/getCategoriesWithRecentAdditions.sql | 12 | ||||
| -rw-r--r-- | gentoobrowse-api/service/sql/portage/getPackagesWithRecentAdditions.sql | 12 | ||||
| -rw-r--r-- | gentoobrowse-api/service/sql/portage/getRecentAdditions.sql | 10 |
7 files changed, 31 insertions, 29 deletions
diff --git a/gentoobrowse-api/api/portage.ice b/gentoobrowse-api/api/portage.ice index 87acd45..c63b0d9 100644 --- a/gentoobrowse-api/api/portage.ice +++ b/gentoobrowse-api/api/portage.ice @@ -19,9 +19,9 @@ module Gentoo { idempotent Ebuilds getPackageVersions(int id); idempotent EbuildDetails getPackageVersionDetails(int id); idempotent ChangeLog getPackageChangeLog(int id); - idempotent Ebuilds getRecentAdditions(int syncs); - idempotent Packages getPackagesWithRecentAdditions(int syncs); - idempotent Categories getCategoriesWithRecentAdditions(int syncs); + idempotent Ebuilds getRecentAdditions(int syncDays); + idempotent Packages getPackagesWithRecentAdditions(int syncDays); + idempotent Categories getCategoriesWithRecentAdditions(int syncDays); idempotent Packages getTopTrackedPackages(int n); idempotent PackageDependencyPackages getPackageDependencies(int id); idempotent PackageDependencyPackages getPackageRuntimeDependencies(int id); diff --git a/gentoobrowse-api/db/schema.sql b/gentoobrowse-api/db/schema.sql index d79a3dd..acf0635 100644 --- a/gentoobrowse-api/db/schema.sql +++ b/gentoobrowse-api/db/schema.sql @@ -751,6 +751,8 @@ CREATE INDEX idx_ebuilddeps_package ON ebuild_deps USING btree (packageid); CREATE INDEX idx_ebuildrdeps_package ON ebuild_rdeps USING btree (packageid); -- Name: idx_ebuilds_firstseen; Type: INDEX; Schema: gentoobrowse; Owner: gentoo; Tablespace: CREATE INDEX idx_ebuilds_firstseen ON ebuilds USING btree (firstseen); +-- Name: idx_ebuilds_firstseen_date; Type: INDEX; Schema: gentoobrowse; Owner: gentoo; Tablespace: +CREATE INDEX idx_ebuilds_firstseen_date ON ebuilds USING btree (date_trunc('day'::text, firstseen) DESC); -- Name: idx_ebuilduses_use; Type: INDEX; Schema: gentoobrowse; Owner: gentoo; Tablespace: CREATE INDEX idx_ebuilduses_use ON ebuild_uses USING btree (use); -- Name: idx_news_fts; Type: INDEX; Schema: gentoobrowse; Owner: gentoo; Tablespace: diff --git a/gentoobrowse-api/service/portageimpl.cpp b/gentoobrowse-api/service/portageimpl.cpp index b05dab7..d74de73 100644 --- a/gentoobrowse-api/service/portageimpl.cpp +++ b/gentoobrowse-api/service/portageimpl.cpp @@ -139,15 +139,15 @@ Portage::getPackageChangeLog(Ice::Int id, const Ice::Current &) } Gentoo::Ebuilds -Portage::getRecentAdditions(Ice::Int syncs, const Ice::Current &) +Portage::getRecentAdditions(Ice::Int syncDays, const Ice::Current &) { - return fetchCache<Gentoo::Ebuilds>(sql::portage::getRecentAdditions, 30, syncs); + return fetchCache<Gentoo::Ebuilds>(sql::portage::getRecentAdditions, 30, syncDays); } Gentoo::Packages -Portage::getPackagesWithRecentAdditions(Ice::Int syncs, const Ice::Current &) +Portage::getPackagesWithRecentAdditions(Ice::Int syncDays, const Ice::Current &) { - return fetchCache<Gentoo::Packages>(sql::portage::getPackagesWithRecentAdditions, 30, syncs); + return fetchCache<Gentoo::Packages>(sql::portage::getPackagesWithRecentAdditions, 30, syncDays); } Gentoo::Packages @@ -175,9 +175,9 @@ Portage::getPackageUrls(Ice::Int id, const Ice::Current &) } Gentoo::Categories -Portage::getCategoriesWithRecentAdditions(Ice::Int syncs, const Ice::Current &) +Portage::getCategoriesWithRecentAdditions(Ice::Int syncDays, const Ice::Current &) { - return fetchCache<Gentoo::Categories>(sql::portage::getCategoriesWithRecentAdditions, 30, syncs); + return fetchCache<Gentoo::Categories>(sql::portage::getCategoriesWithRecentAdditions, 30, syncDays); } Gentoo::PackageDependencyPackages diff --git a/gentoobrowse-api/service/portageimpl.h b/gentoobrowse-api/service/portageimpl.h index 36e4e5a..4d60b19 100644 --- a/gentoobrowse-api/service/portageimpl.h +++ b/gentoobrowse-api/service/portageimpl.h @@ -26,9 +26,9 @@ class DLL_PUBLIC Portage : public Gentoo::Portage, IceTray::AbstractCachingDatab Gentoo::Ebuilds getPackageVersions(Ice::Int id, const Ice::Current &) override; Gentoo::EbuildDetails getPackageVersionDetails(Ice::Int id, const Ice::Current &) override; Gentoo::ChangeLog getPackageChangeLog(Ice::Int id, const Ice::Current &) override; - Gentoo::Ebuilds getRecentAdditions(Ice::Int syncs, const Ice::Current &) override; - Gentoo::Packages getPackagesWithRecentAdditions(Ice::Int syncs, const Ice::Current &) override; - Gentoo::Categories getCategoriesWithRecentAdditions(Ice::Int syncs, const Ice::Current &) override; + Gentoo::Ebuilds getRecentAdditions(Ice::Int syncDays, const Ice::Current &) override; + Gentoo::Packages getPackagesWithRecentAdditions(Ice::Int syncDays, const Ice::Current &) override; + Gentoo::Categories getCategoriesWithRecentAdditions(Ice::Int syncDays, const Ice::Current &) override; Gentoo::Packages getTopTrackedPackages(Ice::Int n, const Ice::Current &) override; Gentoo::PackageDependencyPackages getPackageDependencies(Ice::Int id, const Ice::Current &) override; Gentoo::PackageDependencyPackages getPackageRuntimeDependencies(Ice::Int id, const Ice::Current &) override; diff --git a/gentoobrowse-api/service/sql/portage/getCategoriesWithRecentAdditions.sql b/gentoobrowse-api/service/sql/portage/getCategoriesWithRecentAdditions.sql index 4007bf2..44c5003 100644 --- a/gentoobrowse-api/service/sql/portage/getCategoriesWithRecentAdditions.sql +++ b/gentoobrowse-api/service/sql/portage/getCategoriesWithRecentAdditions.sql @@ -1,11 +1,11 @@ SELECT c.categoryid, c.name, c.summary -FROM gentoobrowse.categories c, gentoobrowse.packages p, gentoobrowse.ebuilds e +FROM gentoobrowse.categories c, gentoobrowse.packages p, gentoobrowse.ebuilds e, ( + SELECT DISTINCT DATE_TRUNC('day', s.firstseen) firstseen + FROM gentoobrowse.ebuilds s + ORDER BY date_trunc('day', s.firstseen) DESC + LIMIT ?) s WHERE c.categoryid = p.categoryid AND p.packageid = e.packageid -AND e.firstseen IN ( - SELECT DISTINCT s.firstseen - FROM gentoobrowse.ebuilds s - ORDER BY s.firstseen DESC - LIMIT ?) +AND DATE_TRUNC('day', e.firstseen) = s.firstseen GROUP BY c.categoryid ORDER BY c.categoryid diff --git a/gentoobrowse-api/service/sql/portage/getPackagesWithRecentAdditions.sql b/gentoobrowse-api/service/sql/portage/getPackagesWithRecentAdditions.sql index 052da8d..a52caf1 100644 --- a/gentoobrowse-api/service/sql/portage/getPackagesWithRecentAdditions.sql +++ b/gentoobrowse-api/service/sql/portage/getPackagesWithRecentAdditions.sql @@ -1,10 +1,10 @@ SELECT p.packageid, p.categoryid, p.name, p.description, p.summary, p.firstseen, p.maintainer, p.maintainername, p.herd -FROM gentoobrowse.packages p, gentoobrowse.ebuilds e -WHERE p.packageid = e.packageid -AND e.firstseen IN ( - SELECT DISTINCT s.firstseen +FROM gentoobrowse.packages p, gentoobrowse.ebuilds e, ( + SELECT DISTINCT DATE_TRUNC('day', s.firstseen) firstseen FROM gentoobrowse.ebuilds s - ORDER BY s.firstseen DESC - LIMIT ?) + ORDER BY date_trunc('day', s.firstseen) DESC + LIMIT ?) s +WHERE p.packageid = e.packageid +AND DATE_TRUNC('day', e.firstseen) = s.firstseen GROUP BY p.packageid ORDER BY p.packageid diff --git a/gentoobrowse-api/service/sql/portage/getRecentAdditions.sql b/gentoobrowse-api/service/sql/portage/getRecentAdditions.sql index ee2812a..a3af9ca 100644 --- a/gentoobrowse-api/service/sql/portage/getRecentAdditions.sql +++ b/gentoobrowse-api/service/sql/portage/getRecentAdditions.sql @@ -1,8 +1,8 @@ SELECT e.ebuildid, e.packageid, e.repoid, e.version, e.slot, e.firstseen, e.moddate, e.license -FROM gentoobrowse.ebuilds e -WHERE e.firstseen IN ( - SELECT DISTINCT s.firstseen +FROM gentoobrowse.ebuilds e, ( + SELECT DISTINCT DATE_TRUNC('day', s.firstseen) firstseen FROM gentoobrowse.ebuilds s - ORDER BY s.firstseen DESC - LIMIT ?) + ORDER BY date_trunc('day', s.firstseen) DESC + LIMIT ?) s +WHERE DATE_TRUNC('day', e.firstseen) = s.firstseen ORDER BY e.firstseen, e.packageid |