diff options
| author | Dan Goodliffe <dan@randomdan.homeip.net> | 2016-09-19 20:52:05 +0100 |
|---|---|---|
| committer | Dan Goodliffe <dan@randomdan.homeip.net> | 2016-09-19 20:52:05 +0100 |
| commit | 65cc9ac3ce8a4048ab7262718b3b85832cbdf01d (patch) | |
| tree | 37c11686d40f3f447a1017f7619937007d000438 /gentoobrowse-api/service/sql | |
| parent | Fix race condition in building related maps (diff) | |
| download | gentoobrowse-api-65cc9ac3ce8a4048ab7262718b3b85832cbdf01d.tar.bz2 gentoobrowse-api-65cc9ac3ce8a4048ab7262718b3b85832cbdf01d.tar.xz gentoobrowse-api-65cc9ac3ce8a4048ab7262718b3b85832cbdf01d.zip | |
Extends API with functions to support website
Diffstat (limited to 'gentoobrowse-api/service/sql')
8 files changed, 45 insertions, 1 deletions
diff --git a/gentoobrowse-api/service/sql/portage/findCategory.sql b/gentoobrowse-api/service/sql/portage/findCategory.sql new file mode 100644 index 0000000..b1cb130 --- /dev/null +++ b/gentoobrowse-api/service/sql/portage/findCategory.sql @@ -0,0 +1,3 @@ +SELECT c.categoryid, c.name, c.summary +FROM gentoobrowse.categories c +WHERE c.name = ? diff --git a/gentoobrowse-api/service/sql/portage/getAllOverviewCategories.sql b/gentoobrowse-api/service/sql/portage/getAllOverviewCategories.sql new file mode 100644 index 0000000..d4b72c8 --- /dev/null +++ b/gentoobrowse-api/service/sql/portage/getAllOverviewCategories.sql @@ -0,0 +1,6 @@ +SELECT c.categoryid, c.name, c.summary, COUNT(p.packageid) packagecount, MAX(p.firstseen) lastaddition +FROM gentoobrowse.categories c + LEFT OUTER JOIN gentoobrowse.packages p + ON p.categoryid = c.categoryid +GROUP BY c.categoryid +ORDER BY c.name diff --git a/gentoobrowse-api/service/sql/portage/getAllPackages.sql b/gentoobrowse-api/service/sql/portage/getAllPackages.sql new file mode 100644 index 0000000..fc8fe6d --- /dev/null +++ b/gentoobrowse-api/service/sql/portage/getAllPackages.sql @@ -0,0 +1,3 @@ +SELECT p.packageid, p.categoryid, p.name, p.description, p.summary, p.firstseen, p.maintainer, p.maintainername, p.herd +FROM gentoobrowse.packages p +ORDER BY p.categoryid, p.name diff --git a/gentoobrowse-api/service/sql/portage/getCategoriesWithRecentAdditions.sql b/gentoobrowse-api/service/sql/portage/getCategoriesWithRecentAdditions.sql new file mode 100644 index 0000000..4007bf2 --- /dev/null +++ b/gentoobrowse-api/service/sql/portage/getCategoriesWithRecentAdditions.sql @@ -0,0 +1,11 @@ +SELECT c.categoryid, c.name, c.summary +FROM gentoobrowse.categories c, gentoobrowse.packages p, gentoobrowse.ebuilds e +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 ?) +GROUP BY c.categoryid +ORDER BY c.categoryid diff --git a/gentoobrowse-api/service/sql/portage/getOverviewCategoriesInSuper.sql b/gentoobrowse-api/service/sql/portage/getOverviewCategoriesInSuper.sql index e971aed..876e551 100644 --- a/gentoobrowse-api/service/sql/portage/getOverviewCategoriesInSuper.sql +++ b/gentoobrowse-api/service/sql/portage/getOverviewCategoriesInSuper.sql @@ -1,4 +1,4 @@ -SELECT c.categoryid, c.name, c.summary, COUNT(p.packageid) packagecount +SELECT c.categoryid, c.name, c.summary, COUNT(p.packageid) packagecount, MAX(p.firstseen) lastaddition FROM gentoobrowse.categories c LEFT OUTER JOIN gentoobrowse.packages p ON p.categoryid = c.categoryid diff --git a/gentoobrowse-api/service/sql/portage/getPackagesWithRecentAdditions.sql b/gentoobrowse-api/service/sql/portage/getPackagesWithRecentAdditions.sql new file mode 100644 index 0000000..052da8d --- /dev/null +++ b/gentoobrowse-api/service/sql/portage/getPackagesWithRecentAdditions.sql @@ -0,0 +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.ebuilds s + ORDER BY s.firstseen DESC + LIMIT ?) +GROUP BY p.packageid +ORDER BY p.packageid diff --git a/gentoobrowse-api/service/sql/portage/getSyncs.sql b/gentoobrowse-api/service/sql/portage/getSyncs.sql new file mode 100644 index 0000000..eef5e3e --- /dev/null +++ b/gentoobrowse-api/service/sql/portage/getSyncs.sql @@ -0,0 +1,5 @@ +SELECT s.firstseen AS timestamp, COUNT(*) AS ebuilds +FROM gentoobrowse.ebuilds s +GROUP BY s.firstseen +ORDER BY s.firstseen DESC +LIMIT ? diff --git a/gentoobrowse-api/service/sql/portage/getTopTrackedPackages.sql b/gentoobrowse-api/service/sql/portage/getTopTrackedPackages.sql new file mode 100644 index 0000000..c328d73 --- /dev/null +++ b/gentoobrowse-api/service/sql/portage/getTopTrackedPackages.sql @@ -0,0 +1,6 @@ +SELECT p.packageid, p.categoryid, p.name, p.description, p.summary, p.firstseen, p.maintainer, p.maintainername, p.herd +FROM gentoobrowse.packages p, gentoobrowse.user_packages up +WHERE p.packageid = up.packageid +GROUP BY p.packageid +ORDER BY COUNT(*) DESC +LIMIT ? |