Update service such that recently ebuilds operations work with sync days, not raw syncs

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