Confirmed users
245
edits
Socorro:Rapid Betas:Database Work: Difference between revisions
→Table Changes
| Line 53: | Line 53: | ||
-- table of adus by product+build_date. not used directly by the UI. | -- table of adus by product+build_date. not used directly by the UI. | ||
create table build_adu ( | <pre>create table build_adu ( | ||
product_version_id int not null, | product_version_id int not null, | ||
build_date date not null, | build_date date not null, | ||
| Line 59: | Line 59: | ||
adu_count INT not null, | adu_count INT not null, | ||
constraint build_adu_key primary key ( product_version_id, build_date, adu_date ) | constraint build_adu_key primary key ( product_version_id, build_date, adu_date ) | ||
); | );</pre> | ||
-- new home page graph table. used directly by the UI | -- new home page graph table. used directly by the UI | ||
CREATE TABLE home_page_graph ( | <pre>CREATE TABLE home_page_graph ( | ||
product_version_id int not null, | product_version_id int not null, | ||
report_date date not null, | report_date date not null, | ||
| Line 70: | Line 70: | ||
crash_hadu numeric not null default 0.0, | crash_hadu numeric not null default 0.0, | ||
constraint home_page_graph_key primary key ( product_version_id, report_date ) | constraint home_page_graph_key primary key ( product_version_id, report_date ) | ||
); | );</pre> | ||
CREATE OR REPLACE VIEW home_page_graph_view | <pre>CREATE OR REPLACE VIEW home_page_graph_view | ||
AS | AS | ||
SELECT product_version_id, | SELECT product_version_id, | ||
| Line 82: | Line 82: | ||
crash_hadu | crash_hadu | ||
FROM home_page_graph | FROM home_page_graph | ||
JOIN product_versions USING (product_version_id); | JOIN product_versions USING (product_version_id);</pre> | ||
-- home page graph table by build. used directly by the UI to show graph | -- home page graph table by build. used directly by the UI to show graph | ||
-- by build date for nightly, aurora, rapid beta | -- by build date for nightly, aurora, rapid beta | ||
CREATE TABLE home_page_graph_build ( | <pre>CREATE TABLE home_page_graph_build ( | ||
product_version_id int not null, | product_version_id int not null, | ||
report_date date not null, | report_date date not null, | ||
| Line 95: | Line 95: | ||
crash_hadu numeric not null default 0.0, | crash_hadu numeric not null default 0.0, | ||
constraint home_page_graph_key primary key ( product_version_id, report_date ) | constraint home_page_graph_key primary key ( product_version_id, report_date ) | ||
); | );</pre> | ||
CREATE OR REPLACE VIEW home_page_graph_build_view | <pre>CREATE OR REPLACE VIEW home_page_graph_build_view | ||
AS | AS | ||
SELECT product_version_id, | SELECT product_version_id, | ||
| Line 110: | Line 110: | ||
JOIN product_release_channels ON | JOIN product_release_channels ON | ||
product_versions.product_name = product_release_channels.product_name | product_versions.product_name = product_release_channels.product_name | ||
AND product_versions.build_type = product_release_channels.release_channel; | AND product_versions.build_type = product_release_channels.release_channel;</pre> | ||
-- new "daily graph" table, i.e. "crashes per user" searchable graph | -- new "daily graph" table, i.e. "crashes per user" searchable graph | ||
CREATE TABLE crashes_by_user ( | <pre>CREATE TABLE crashes_by_user ( | ||
product_version_id int not null, | product_version_id int not null, | ||
os_short_name citext not null, | os_short_name citext not null, | ||
| Line 121: | Line 121: | ||
report_count int not null, | report_count int not null, | ||
adu 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 ) | CONSTRAINT crashes_by_user_key PRIMARY KEY ( product_version_id, report_date, os_short_name, crash_type_id )</pre> | ||
-- view on top of that to support easy querying | -- view on top of that to support easy querying | ||
CREATE OR REPLACE VIEW crashes_by_user_view AS | <pre>CREATE OR REPLACE VIEW crashes_by_user_view AS | ||
SELECT crashes_by_user.product_version_id, | SELECT crashes_by_user.product_version_id, | ||
product_versions.product_name, version_string, | product_versions.product_name, version_string, | ||
| Line 136: | Line 136: | ||
AND product_versions.build_type = product_release_channels.release_channel | AND product_versions.build_type = product_release_channels.release_channel | ||
JOIN os_names USING (os_short_name) | JOIN os_names USING (os_short_name) | ||
JOIN crash_types USING (crash_type_id); | JOIN crash_types USING (crash_type_id);</pre> | ||
-- same thing, except by build | -- same thing, except by build | ||
| Line 156: | Line 156: | ||
-- a bit slower | -- a bit slower | ||
CREATE OR REPLACE VIEW crashes_by_user_build_view AS | <pre>CREATE OR REPLACE VIEW crashes_by_user_build_view AS | ||
SELECT crashes_by_user_build.product_version_id, | SELECT crashes_by_user_build.product_version_id, | ||
product_versions.product_name, version_string, | product_versions.product_name, version_string, | ||
| Line 173: | Line 173: | ||
product_versions.product_name, version_string, | product_versions.product_name, version_string, | ||
os_short_name, os_name, crash_type, crash_type_short, | os_short_name, os_name, crash_type, crash_type_short, | ||
build_date, throttle; | build_date, throttle;</pre> | ||
-- tcbs, only by build: | -- tcbs, only by build: | ||
| Line 206: | Line 206: | ||
-- useful functions | -- useful functions | ||
crash_hadu ( | <pre>crash_hadu ( | ||
crashes bigint, | crashes bigint, | ||
adu bigint, | adu bigint, | ||
throttle numeric optional (1.0) | throttle numeric optional (1.0) | ||
); | );</pre> | ||
does the math for producing "crashes per hundred ADU" for you, rounding to 3 decimal places. | does the math for producing "crashes per hundred ADU" for you, rounding to 3 decimal places. | ||