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 TABLE crashes_by_user (
 product_version_id int not null,
 os_short_name citext not null,
 crash_type_id int not null references crash_types(crash_type_id),
 report_date date not null,
 report_count int not null,
 adu int not null,
 CONSTRAINT crashes_by_user_key PRIMARY KEY ( product_version_id, report_date, os_short_name, crash_type_id )

-- view on top of that to support easy querying

CREATE OR REPLACE VIEW crashes_by_user_view AS
SELECT crashes_by_user.product_version_id, 
  product_versions.product_name, version_string, 
  os_short_name, os_name, crash_type, crash_type_short, report_date,
  report_count, (report_count / throttle) as adjusted_report_count,
  adu, throttle
FROM crashes_by_user
  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)
  JOIN crash_types USING (crash_type_id);

-- same thing, except by build

CREATE TABLE crashes_by_user_build (
 product_version_id int not null,
 os_short_name citext not null,
 crash_type_id int not null references crash_types(crash_type_id),
 build_date date not null,
 report_date date not null,
 report_count int not null,
 adu int not null,
 CONSTRAINT crashes_by_user_build_key PRIMARY KEY ( product_version_id, build_date, report_date, os_short_name, crash_type_id )

-- view on top of that to support easy querying -- note that this is based on a aggregate, so its -- a bit slower

CREATE OR REPLACE VIEW crashes_by_user_build_view AS
SELECT crashes_by_user_build.product_version_id, 
  product_versions.product_name, version_string, 
  os_short_name, os_name, crash_type, crash_type_short, build_date,
  sum(report_count) as report_count, 
  sum(report_count / throttle) as adjusted_report_count,
  sum(adu) as adu, throttle
FROM crashes_by_user_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
  JOIN os_names USING (os_short_name)
  JOIN crash_types USING (crash_type_id)
GROUP BY crashes_by_user_build.product_version_id, 
  product_versions.product_name, version_string, 
  os_short_name, os_name, crash_type, crash_type_short, 
  build_date, throttle;

-- tcbs, only by build:

	CREATE TABLE tcbs_build (
		signature_id integer NOT NULL,
		build_date date NOT NULL,
		report_date date NOT NULL,
		product_version_id integer NOT NULL,
		process_type citext NOT NULL,
		release_channel citext NOT NULL,
		report_count integer DEFAULT 0 NOT NULL,
		win_count integer DEFAULT 0 NOT NULL,
		mac_count integer DEFAULT 0 NOT NULL,
		lin_count integer DEFAULT 0 NOT NULL,
		hang_count integer DEFAULT 0 NOT NULL,
		startup_count integer,
		CONSTRAINT tcbs_build_key PRIMARY KEY
			(product_version_id, report_date, build_date, process_type, signature_id)
	);

-- other table changes, not visible to UI products:

   add "rapid_beta_version" column

product_info, product_selector:

   add "has_builds" column

   this column allows you to determine which product/versions are available
   for "by build date" views.

-- useful functions

crash_hadu (
   crashes bigint,
   adu bigint,
   throttle numeric optional (1.0)
   );

does the math for producing "crashes per hundred ADU" for you, rounding to 3 decimal places.

Plan for Handling Rapid and Individual Betas

For product/versions with a rapid beta, we will:

1. add a product_version with just a "b"
2. summarize crashes for this "b" version as the total of all crashes for all betas in that series, for:
   • home_page_graph
   • crash_graph
   • tcbs
3. if a user clicks on a signature in tcbs, the UI will do a multiselect in order to display signature_summary.