Socorro:Rapid Betas:Database Work

Outline of tasks to be performed:

1. migrate productdims to product_versions
   1. select versions to migrate
      1. test migrating all versions after FF 3.0, first
      2. if that doesn't work, select versions
   2. copy versions into product_versions
   3. modify product_info, similar views
   4. modify update_products()
   5. modify update_reports_clean()
2. purge oldtcbs
   1. disable old cron jobs -- rhelmer
   2. drop oldtcbs tables
      • alexa_topsites
      • builds
      • frames
      • osdims
      • product_visibility
      • productdims
      • productdims_version_sort
      • release_build_type_map
      • signature_build
      • signature_first
      • signature_bugs_rollup
      • signature_productdims
      • top_crashes_by_signature
      • top_crashes_by_url
      • top_crashes_by_url_signature
      • urldims
3. create testable dump of database
   1. create per-object dump of above
   2. clean up per-object dump
   3. create automated load script for the database
   4. create test data for automated load script
   5. add test framework loader (pgtap).
4. overhaul tcbs, daily crash views
   1. create home_page_graph view
   2. combine/normalize product_adu + daily_crashes.
   3. add remaining TCBS data to tcbs view
      1. use set-returning function?
   4. modify cron jobs
5. Accomodate rapid beta versions
   1. add column to products
   2. modify update_products()
   3. modify update_reports_clean()
6. Create new by-build views
   1. create home_page_graph_build & SPs
   2. create daily_graph_build & SPs
   3. create tcbs_build & SPs

Table Changes

-- table of adus by product+build_date. not used directly by the UI.

create table build_adu (

   product_version_id int not null,
   build_date date not null,
   adu_date DATE not null,
   adu_count INT not null,
   constraint build_adu_key primary key ( product_version_id, build_date, adu_date )

);

-- new home page graph table. used directly by the UI

CREATE TABLE home_page_graph (

 product_version_id int not null,
 report_date date not null,
 report_count int not null default 0,
 adu int not null default 0,
 crash_hadu numeric not null default 0.0,
 constraint home_page_graph_key primary key ( product_version_id, report_date )

);

CREATE OR REPLACE VIEW home_page_graph_view AS SELECT product_version_id,

 product_name,
 version_string,
 report_date,
 report_count,
 adu,
 crash_hadu

FROM home_page_graph

 JOIN product_versions USING (product_version_id);

-- home page graph table by build. used directly by the UI to show graph -- by build date for nightly, aurora, rapid beta

CREATE TABLE home_page_graph_build (

 product_version_id int not null,
 report_date date not null,
 build_date date not null,
 report_count int not null default 0,
 adu int not null default 0,
 crash_hadu numeric not null default 0.0,
 constraint home_page_graph_key primary key ( product_version_id, report_date )

);

CREATE OR REPLACE VIEW home_page_graph_build_view AS SELECT product_version_id,

 product_name,
 version_string,
 build_date,
 sum(report_count) as report_count,
 sum(adu) as adu,
 ( sum(report_count) * throttle * 100 ) / sum(adu) as crash_hadu

FROM home_page_graph_build

 JOIN product_versions USING (product_version_id)
 JOIN product_release_channels ON
      product_versions.product_name = product_release_channels.product_name
         AND product_versions.build_type = product_release_channels.release_channel;

-- new "daily graph" table, i.e. "crashes per user" searchable graph

CREATE ENUM crash_type (

 'crash browser','oop plugin','hang browser','hang plugin', 'content' );

CREATE TABLE crash_graph (

 product_version_id int not null,
 os_short_name citext not null,
 crash_type crash_type not null,
 crash_date date not null,
 report_count int not null,
 adu int not null

);

-- view on top of that to support easy querying

CREATE OR REPLACE VIEW crash_graph_view AS SELECT crash_graph.product_version_id, product_name, version_string,

 os_short_name, os_name, crash_type, crash_date,
 report_count, (report_count / throttle) as adjusted_report_count,
 adu_count, throttle

FROM crash_graph

 JOIN product_versions USING (product_version_id)
 JOIN product_release_channels ON
   product_versions.product_name = product_release_channels.product_name
   AND product_versions.build_type = product_release_channels.release_channel
 JOIN os_names USING (os_short_name);

-- same thing, except by build

CREATE TABLE crash_graph_build (

 product_version_id int not null,
 os_short_name citext not null,
 crash_type crash_type not null,
 build_date date not null,
 report_count int not null,
 adu int not null

);

-- view on top of that to support easy querying

CREATE OR REPLACE VIEW crash_graph_build_view AS SELECT crash_graph.product_version_id, product_name, version_string,

 os_short_name, os_name, crash_type, build_date,
 report_count, (report_count / throttle) as adjusted_report_count,
 adu_count, throttle

FROM crash_graph

 JOIN product_versions USING (product_version_id)
 JOIN product_release_channels ON
   product_versions.product_name = product_release_channels.product_name
   AND product_versions.build_type = product_release_channels.release_channel
 JOIN os_names USING (os_short_name);

-- other table changes, not visible to UI

tcbs:

   drop "release_channel" column

products:

   add "rapid_beta_version" column