important assumption: Ensure that the buildid includes the start date for the data you're backfilling. There are some restrictions based on "build date", which is extracted from the buildid, deep in the stored procedures. If your buildid resolves to a date that is prior to the date you're testing, none of the matviews will yield results.
- Add a fake row to releases_raw
INSERT into releases_raw (product_name, version, platform, build_id, build_type, beta_number, repository, update_channel) VALUES('firefox', '27.0', 'win32', '20140136130000', 'beta', 99, 'mozilla-beta', 'beta');
product_name | version | platform | build_id | build_type | beta_number | repository | update_channel | version_build
--------------+---------+----------+----------------+------------+-------------+--------------+----------------+---------------
firefox | 27.0 | win32 | 20140123130000 | beta | 99 | mozilla-beta | beta |
1a. add data for raw_adu
INSERT into raw_adu (adu_count, date, product_name, product_os_platform, product_version, build, build_channel, product_guid, received_at, update_channel) VALUES (100000, '2014-01-23', 'Firefox', 'Windows', '27.0', '20140123130000', 'beta', 'ec8030f7-c20a-464f-9b0e-13a3a9e97384', now(), 'beta');
- Run
update_product_versions(number_of_days)
-- where number_of_days equals the number of days in the past you want the query to pull builds from.
breakpad=# select * from product_versions order by product_version_id desc limit 1;
-[ RECORD 1 ]------+--------------
product_version_id | 2124
product_name | Firefox
major_version | 27.0
release_version | 27.0
version_string | 27.0b99
beta_number | 99
version_sort | 027000000b099
build_date | 2014-01-26
sunset_date | 2014-02-10
featured_version | f
build_type | beta
has_builds | f
is_rapid_beta | f
rapid_beta_id | 2029
build_type_enum | beta
version_build |
And:
breakpad=# select * from product_version_builds order by product_version_id desc limit 1;
-[ RECORD 1 ]------+---------------
product_version_id | 2124
build_id | 20140126130000
platform | win32
repository | mozilla-beta
- Update a few reports to use the appropriate build_id.
update reports set build = '20140123130000' WHERE uuid IN (select uuid from reports where date_processed between '2014-01-23' and '2014-01-28' AND product = 'Firefox' and version ~ '^27.0' and release_channel = 'beta' limit 1000) and date_processed between '2014-01-23' and '2014-01-28';
In this case, this only affected 2014-01-23 data. I updated 1000 reports for that day.
- Run a backfill on the affected dates.
select backfill_matviews('2014-01-23', '2014-01-24');