|
CREATE OR REPLACE FUNCTION update_product_versions( |
|
product_window integer DEFAULT 30 |
|
) |
|
RETURNS boolean |
|
LANGUAGE plpgsql |
|
SET work_mem TO '512MB' |
|
SET maintenance_work_mem TO '512MB' |
|
AS $$ |
|
BEGIN |
|
-- daily batch update function for new products and versions |
|
-- reads data from releases_raw, cleans it |
|
-- and puts data corresponding to the new versions into |
|
-- product_versions and related tables |
|
|
|
-- is cumulative and can be run repeatedly without issues |
|
-- * covers FennecAndroid and ESR releases |
|
-- * now only compares releases from the last 30 days |
|
-- * restricts to only the canonical "repositories" |
|
-- * covers webRT |
|
-- * covers rapid betas, but no more final betas |
|
|
|
-- create temporary table, required because |
|
-- all of the special cases |
|
|
|
|
|
create temporary table releases_recent |
|
ON commit drop |
|
AS |
|
select COALESCE ( specials.product_name, products.product_name ) |
|
AS product_name, |
|
releases_raw.version, |
|
releases_raw.beta_number, |
|
releases_raw.build_id, |
|
releases_raw.update_channel, |
|
releases_raw.platform, |
|
(major_version_sort(version) >= major_version_sort(rapid_release_version)) |
|
AS is_rapid, |
|
is_rapid_beta(releases_raw.update_channel, version, rapid_beta_version::major_version) |
|
AS is_rapid_beta, |
|
releases_raw.repository |
|
FROM releases_raw |
|
JOIN products ON releases_raw.product_name = products.release_name |
|
JOIN release_repositories |
|
ON releases_raw.repository = release_repositories.repository |
|
LEFT OUTER JOIN special_product_platforms AS specials |
|
ON releases_raw.platform::citext = specials.platform |
|
AND releases_raw.product_name = specials.release_name |
|
AND releases_raw.repository = specials.repository |
|
AND releases_raw.update_channel = specials.release_channel |
|
AND major_version_sort(version) >= major_version_sort(min_version) |
|
WHERE |
|
build_date(build_id) > (current_date - product_window) |
|
AND version_matches_channel(releases_raw.version, |
|
releases_raw.update_channel::citext); |
|
|
|
-- fix ESR versions |
|
|
|
UPDATE releases_recent |
|
SET update_channel = 'esr' |
|
WHERE update_channel ILIKE 'release' |
|
AND version ILIKE '%esr'; |
|
|
|
-- insert WebRT "releases", which are copies of Firefox releases |
|
-- insert them only if the FF release is greater than the first |
|
-- release for WebRT |
|
|
|
INSERT INTO releases_recent |
|
SELECT 'WebappRuntime', |
|
version, |
|
beta_number, |
|
build_id, |
|
update_channel, |
|
platform, |
|
is_rapid, |
|
is_rapid_beta, |
|
repository |
|
FROM releases_recent |
|
JOIN products |
|
ON products.product_name = 'WebappRuntime' |
|
WHERE releases_recent.product_name = 'Firefox' |
|
AND major_version_sort(releases_recent.version) |
|
>= major_version_sort(products.rapid_release_version); |
|
|
|
-- insert WebRTmobile "releases", which are copies of Fennec releases |
|
-- insert them only if the Fennec release is greater than the first |
|
-- release for WebRTmobile |
|
|
|
INSERT INTO releases_recent |
|
SELECT 'WebappRuntimeMobile', |
|
version, |
|
beta_number, |
|
build_id, |
|
update_channel, |
|
platform, |
|
is_rapid, |
|
is_rapid_beta, |
|
repository |
|
FROM releases_recent |
|
JOIN products |
|
ON products.product_name = 'WebappRuntimeMobile' |
|
WHERE releases_recent.product_name = 'Fennec' |
|
AND major_version_sort(releases_recent.version) |
|
>= major_version_sort(products.rapid_release_version); |
|
|
|
-- insert MetroFirefox "releases", which are copies of Firefox releases |
|
-- insert them only if the FF release is greater than the first |
|
-- release for WebRT |
|
|
|
INSERT INTO releases_recent |
|
SELECT 'MetroFirefox', |
|
version, |
|
beta_number, |
|
build_id |
|
update_channel, |
|
platform, |
|
is_rapid, |
|
is_rapid_beta, |
|
repository |
|
FROM releases_recent |
|
JOIN products |
|
ON products.product_name = 'MetroFirefox' |
|
WHERE releases_recent.product_name = 'Firefox' |
|
AND major_version_sort(releases_recent.version) |
|
>= major_version_sort(products.rapid_release_version); |
|
|
|
-- now put it in product_versions |
|
-- first releases, aurora and nightly and non-rapid betas |
|
|
|
insert into product_versions ( |
|
product_name, |
|
major_version, |
|
release_version, |
|
version_string, |
|
beta_number, |
|
version_sort, |
|
build_date, |
|
sunset_date, |
|
build_type, |
|
has_builds, |
|
build_type_enum |
|
) |
|
select releases_recent.product_name, |
|
to_major_version(version), |
|
version, |
|
version_string(version, releases_recent.beta_number), |
|
releases_recent.beta_number, |
|
version_sort(version, releases_recent.beta_number), |
|
build_date(min(build_id)), |
|
sunset_date(min(build_id), releases_recent.update_channel), |
|
releases_recent.update_channel::citext, |
|
(releases_recent.update_channel IN ('aurora', 'nightly')), |
|
releases_recent.update_channel::build_type_enum as build_type_enum |
|
from releases_recent |
|
left outer join product_versions ON |
|
( releases_recent.product_name = product_versions.product_name |
|
AND releases_recent.version = product_versions.release_version |
|
AND releases_recent.beta_number IS NOT DISTINCT FROM product_versions.beta_number ) |
|
where is_rapid |
|
AND product_versions.product_name IS NULL |
|
AND NOT releases_recent.is_rapid_beta |
|
group by releases_recent.product_name, version, |
|
releases_recent.beta_number, |
|
releases_recent.update_channel::citext, releases_recent.update_channel; |
|
|
|
-- insert rapid betas "parent" products |
|
-- these will have a product, but no builds |
|
|
|
insert into product_versions ( |
|
product_name, |
|
major_version, |
|
release_version, |
|
version_string, |
|
beta_number, |
|
version_sort, |
|
build_date, |
|
sunset_date, |
|
build_type, |
|
is_rapid_beta, |
|
has_builds, |
|
build_type_enum |
|
) |
|
select products.product_name, |
|
to_major_version(version), |
|
version, |
|
version || 'b', |
|
0, |
|
version_sort(version, 0), |
|
build_date(min(build_id)), |
|
sunset_date(min(build_id), 'beta' ), |
|
'beta', |
|
TRUE, |
|
TRUE, |
|
'beta' |
|
from releases_recent |
|
join products ON releases_recent.product_name = products.release_name |
|
left outer join product_versions ON |
|
( releases_recent.product_name = product_versions.product_name |
|
AND releases_recent.version = product_versions.release_version |
|
AND product_versions.beta_number = 0 ) |
|
where is_rapid |
|
and releases_recent.is_rapid_beta |
|
and product_versions.product_name IS NULL |
|
group by products.product_name, version; |
|
|
|
-- finally, add individual betas for rapid_betas |
|
-- these need to get linked to their master rapid_beta |
|
|
|
insert into product_versions ( |
|
product_name, |
|
major_version, |
|
release_version, |
|
version_string, |
|
beta_number, |
|
version_sort, |
|
build_date, |
|
sunset_date, |
|
build_type, |
|
rapid_beta_id, |
|
build_type_enum |
|
) |
|
select products.product_name, |
|
to_major_version(version), |
|
version, |
|
version_string(version, releases_recent.beta_number), |
|
releases_recent.beta_number, |
|
version_sort(version, releases_recent.beta_number), |
|
build_date(min(build_id)), |
|
rapid_parent.sunset_date, |
|
'beta', |
|
rapid_parent.product_version_id, |
|
'beta' |
|
from releases_recent |
|
join products ON releases_recent.product_name = products.release_name |
|
left outer join product_versions ON |
|
( releases_recent.product_name = product_versions.product_name |
|
AND releases_recent.version = product_versions.release_version |
|
AND product_versions.beta_number = releases_recent.beta_number ) |
|
join product_versions as rapid_parent ON |
|
releases_recent.version = rapid_parent.release_version |
|
and releases_recent.product_name = rapid_parent.product_name |
|
and rapid_parent.is_rapid_beta |
|
where is_rapid |
|
and releases_recent.is_rapid_beta |
|
and product_versions.product_name IS NULL |
|
group by products.product_name, version, rapid_parent.product_version_id, |
|
releases_recent.beta_number, rapid_parent.sunset_date; |
|
|
|
-- add build ids |
|
-- note that rapid beta parent records will have no buildids of their own |
|
|
|
insert into product_version_builds |
|
(product_version_id, build_id, platform, repository) |
|
select distinct product_versions.product_version_id, |
|
releases_recent.build_id, |
|
releases_recent.platform, |
|
releases_recent.repository |
|
from releases_recent |
|
join product_versions |
|
ON releases_recent.product_name = product_versions.product_name |
|
AND releases_recent.version = product_versions.release_version |
|
AND releases_recent.update_channel = product_versions.build_type |
|
AND ( releases_recent.beta_number IS NOT DISTINCT FROM product_versions.beta_number ) |
|
left outer join product_version_builds ON |
|
product_versions.product_version_id = product_version_builds.product_version_id |
|
AND releases_recent.build_id = product_version_builds.build_id |
|
AND releases_recent.platform = product_version_builds.platform |
|
where product_version_builds.product_version_id is null; |
|
|
|
drop table releases_recent; |
|
|
|
RETURN TRUE; |
|
END; |
|
$$; |