|
schema
Describe current database schema
(See bottom of page for inline graphic) Tables used primarily when processing JobsReports (Partitioned)Reports table contains the 'cooked' data received from breakpad and abstracted. Data from this table is further transformed into 'materialized views' (see below). Reports is unchanged from prior version. CREATE TABLE reports ( id serial NOT NULL PRIMARY KEY, client_crash_date timestamp with time zone, date_processed timestamp without time zone, uuid character varying(50) NOT NULL UNIQUE, product character varying(30), version character varying(16), build character varying(30), signature character varying(255), url character varying(255), install_age integer, last_crash integer, uptime integer, cpu_name character varying(100), cpu_info character varying(100), reason character varying(255), address character varying(20), os_name character varying(100), os_version character varying(100), email character varying(100), -- Now always NULL or empty build_date timestamp without time zone, user_id character varying(50), -- Now always NULL or empty started_datetime timestamp without time zone, completed_datetime timestamp without time zone, success boolean, truncated boolean, processor_notes text, user_comments character varying(1024), app_notes character varying(1024), distributor character varying(20), distributor_version character varying(20) ); Indices are on child/partition tables, not base table index: date_processed index: uuid index: signature index: url index: (product,version) index: (uuid, date_processed) index: (signature, date_processed) ProcessorsProcessors table keeps track of the current state of the processor that pull things out of the file system and into the reports database. Processors is unchanged from prior version. CREATE TABLE processors ( id serial NOT NULL PRIMARY KEY, name varchar(255) NOT NULL UNIQUE, startdatetime timestamp without time zone NOT NULL, lastseendatetime timestamp without time zone ); JobsJobs table holds data about jobs that are queued for the processors to handle. Jobs is unchanged from prior version. CREATE TABLE jobs ( id serial NOT NULL PRIMARY KEY, pathname character varying(1024) NOT NULL, uuid varchar(50) NOT NULL UNIQUE, owner integer, priority integer DEFAULT 0, queueddatetime timestamp without time zone, starteddatetime timestamp without time zone, completeddatetime timestamp without time zone, success boolean, message text, FOREIGN KEY (owner) REFERENCES processors (id) on delete cascade ); index: owner index: (owner, starteddatetime) index (completeddatetime, priority DESC) Priority JobsPriority Jobs table is used to mark rows in the jobs table that need to be processed soon. Priority Jobs is unchanged from prior versions. CREATE TABLE priortyjobs ( uuid varchar(255) NOT NULL PRIMARY KEY ); Tables primarily used during data extractionBranchesBranches table associates a product and version with with the gecko version (called 'branch') CREATE TABLE branches ( product character varying(30) NOT NULL, version character varying(16) NOT NULL, branch character varying(24) NOT NULL ); Extensions (Partitioned)Extensions table associates a report with the extensions on the crashing application. Extensions is unchanged from prior version. (Not now in use) CREATE TABLE extensions ( report_id integer NOT NULL, -- Foreign key references parallel reports partition(id) date_processed timestamp without time zone, extension_key integer NOT NULL, extension_id character varying(100) NOT NULL, extension_version character varying(16), FOREIGN KEY (report_id) REFERENCES reports_<partition>(id) on delete cascade ); Index is on child/partition tables, not base table index: (report_id,date_processed) Frames (Partitioned)Frames table associates a report with the stack frames and their signatures that were seen in the crashing application. Frames is unchanged from prior version. CREATE TABLE frames ( report_id integer NOT NULL, date_processed timestamp without time zone, frame_num integer NOT NULL, signature varchar(255) FOREIGN KEY (report_id) REFERENCES reports_<partition>(id) on delete cascade ); Index is on child/partition tables, not base table index: (report_id,date_processed) Tables primarily used for materialized viewsproduct visibilityProduct visibility controls which products are subject to having data aggregated into the various materialized views. Replaces mtbfconfig, tcbyurlconfig CREATE TABLE product_visibility ( productdims_id integer NOT NULL PRIMARY KEY, start_date timestamp, -- set this manually for all mat views end_date timestamp, -- set this manually: Used by mat views that care ignore boolean default False, -- force aggregation off for this product id FOREIGN KEY (productdims_id) REFERENCES productdims(id) ); index: end_date index: start_date Dimensions tablessignaturedimsSignature dims was a table associating signature with id, no longer used. Instead, signatures are stored directly in the places that need them. productdimsProduct dims associates a product, version and release key. An enum is used for the release key. Product dims has changed from prior version by dropping the os_name column, which has been promoted into its own osdims table. CREATE TYPE release_enum AS ENUM ('major', 'milestone', 'development');"CREATE TABLE productdims ( id serial NOT NULL PRIMARY KEY, product TEXT NOT NULL, -- varchar(30) version TEXT NOT NULL, -- varchar(16) release release_enum -- 'major':x.y.z..., 'milestone':x.ypre, 'development':x.y[ab]z ); unique index: (product,version) index: release osdimsOS dims associates an os name and version. Promoted from earlier versions where os_name was stored directly in 'facts' tables. CREATE TABLE osdims ( id serial NOT NULL PRIMARY KEY, os_name CHARACTER VARYING(100) NOT NULL, os_version CHARACTER VARYING(100) ); index: (os_name,os_version) urldimsURL dims associates a domain and a simplified url. URL dims is unchanged from prior version. CREATE TABLE urldims ( id serial NOT NULL, domain character varying(255) NOT NULL, url character varying(255) NOT NULL ); unique index: (url,domain) View tablesView tables now have a uniform layout:
time before failureAggregate the amount of time the app ran from startup to fail, and from prior fail to current fail. Replaces mtbffacts table. CREATE TABLE time_before_failure ( id serial NOT NULL PRIMARY KEY, sum_uptime_seconds integer NOT NULL, report_count integer NOT NULL, productdims_id integer, osdims_id integer, window_end TIMESTAMP WITHOUT TIME ZONE NOT NULL, window_size INTERVAL NOT NULL, FOREIGN KEY (productdims_id) REFERENCES productdims(id), FOREIGN KEY (osdims_id) REFERENCES osdims(id) ); index: (window_end,window_size) index: productdims_id index: osdims_id top crashes by signatureAggregate the number of crashes per unit of time associated with a particular stack signature. Replaces topcrashers table. CREATE TABLE top_crashes_by_signature ( id serial NOT NULL PRIMARY KEY, count integer NOT NULL DEFAULT 0, uptime real DEFAULT 0.0, signature TEXT, productdims_id integer, osdims_id integer, window_end TIMESTAMP WITHOUT TIME ZONE NOT NULL, window_size INTERVAL NOT NULL, FOREIGN KEY (productdims_id) REFERENCES productdims(id), FOREIGN KEY (osdims_id) REFERENCES osdims(id) ); index: productdims_id index: osdims_id index: signature index: (window_end,window_size) top crashes by urlAggregate the number of crashes associated with a particular URL. Replaces topcrashurlfacts table. CREATE TABLE top_crashes_by_url ( id serial NOT NULL PRIMARY KEY, count integer NOT NULL, urldims_id integer, productdims_id integer, osdims_id integer, window_end TIMESTAMP WITHOUT TIME ZONE NOT NULL, window_size INTERVAL NOT NULL, FOREIGN KEY (urldims_id) REFERENCES urldims(id) FOREIGN KEY (productdims_id) REFERENCES productdims(id), FOREIGN KEY (osdims_id) REFERENCES osdims(id) ); index: count index: urldims_id index: productdims_id index: osdims_id index: (window_end,window_size) top crashes by url signatureAssociate top crashes by url with their signature(s). Promoted from prior topcrashurlfacts where signaturedims id was stored directly. Use of this table allows multiple signatures to be associated with the same crashing url. CREATE TABLE top_crashes_by_url_signature ( top_crashes_by_url_id integer NOT NULL, -- foreign key signature TEXT NOT NULL, count integer NOT NULL, FOREIGN KEY (top_crashes_by_url_id) REFERENCES crashes_by_url(id) ); primary key: (top_crashes_by_url_id,signature) top crash url facts reportsAssociate a crash uuid and comment with a particular top crash by url row. This table's schema is unchanged from prior version, but the topcrashurlfacts_id column is re-purposed to map to the new top_crashes_by_url table. CREATE TABLE topcrashurlfactsreports ( id serial NOT NULL PRIMARY KEY, uuid character varying(50) NOT NULL, comments character varying(500), topcrashurlfacts_id integer FOREIGN KEY (topcrashurlfacts_id) REFERENCES top_crashes_by_url(id) ); index: topcrashurlfacts_id Bug trackingbugsPeriodically extract new and changed items from the bug tracking database. Bugs is recently added. CREATE TABLE bugs ( id int NOT NULL PRIMARY KEY, status text, resolution text, short_desc text ); bug associationsAssociate signatures with bug ids. Bug Associations is recently added. CREATE TABLE bug_associations ( signature text NOT NULL, bug_id int NOT NULL, FOREIGN KEY (bug_id) REFERENCES bugs(id) ); primary key: (signature, bug_id) index: bug_id Meta dataServer statusServer Status table keeps track of the current status of jobs processors. Server status is unchanged from prior version. CREATE TABLE server_status ( id serial NOT NULL PRIMARY KEY, date_recently_completed timestamp without time zone, date_oldest_job_queued timestamp without time zone, avg_process_sec real, avg_wait_sec real, waiting_job_count integer NOT NULL, processors_count integer NOT NULL, date_created timestamp without time zone NOT NULL ); index: (date_created,id)
PDF of the schema scales nicely. |
Sign in to add a comment
