'
BEGIN TRANSACTION;

--
-- Table: accepted_reports
--
CREATE TABLE accepted_reports (
  _relational_id00 INTEGER PRIMARY KEY NOT NULL,
  id varchar NOT NULL,
  status varchar
);

CREATE INDEX pk__relational_id00 ON accepted_reports (_relational_id00);

--
-- Table: distributions
--
CREATE TABLE distributions (
  id INTEGER PRIMARY KEY NOT NULL,
  name varchar NOT NULL,
  version varchar NOT NULL
);

CREATE INDEX pk_id ON distributions (id);

--
-- Table: environments
--
CREATE TABLE environments (
  id INTEGER PRIMARY KEY NOT NULL,
  user_agent varchar,
  language_id int NOT NULL,
  system_id int NOT NULL,
  FOREIGN KEY (language_id) REFERENCES languages(id),
  FOREIGN KEY (system_id) REFERENCES systems(id)
);

CREATE INDEX pk_id02 ON environments (id);

--
-- Table: environment_toolchains
--
CREATE TABLE environment_toolchains (
  id INTEGER PRIMARY KEY NOT NULL,
  key varchar,
  value varchar,
  toolchain_id int NOT NULL,
  FOREIGN KEY (toolchain_id) REFERENCES environments(id)
);

CREATE INDEX pk_id03 ON environment_toolchains (id);

--
-- Table: languages
--
CREATE TABLE languages (
  archname varchar NOT NULL,
  build varchar,
  commit_id varchar,
  id INTEGER PRIMARY KEY NOT NULL,
  implementation varchar,
  name enum NOT NULL,
  version varchar NOT NULL,
  backend_id int,
  FOREIGN KEY (backend_id) REFERENCES language_backends(id)
);

CREATE INDEX pk_id04 ON languages (id);

--
-- Table: language_backends
--
CREATE TABLE language_backends (
  engine varchar,
  id INTEGER PRIMARY KEY NOT NULL,
  version varchar
);

CREATE INDEX pk_id05 ON language_backends (id);

--
-- Table: language_variables
--
CREATE TABLE language_variables (
  id INTEGER PRIMARY KEY NOT NULL,
  key varchar,
  value varchar,
  variable_id int NOT NULL,
  FOREIGN KEY (variable_id) REFERENCES languages(id)
);

CREATE INDEX pk_id06 ON language_variables (id);

--
-- Table: prerequisites
--
CREATE TABLE prerequisites (
  have varchar,
  id INTEGER PRIMARY KEY NOT NULL,
  name varchar NOT NULL,
  need varchar NOT NULL,
  phase varchar NOT NULL,
  prerequisite_id int NOT NULL,
  FOREIGN KEY (prerequisite_id) REFERENCES distributions(id)
);

CREATE INDEX pk_id07 ON prerequisites (id);

--
-- Table: releases
--
CREATE TABLE releases (
-- Comments: 
-- A summary of test reports for a single CPAN release
--

  -- The CPAN ID of the author who released this version of this distribution
  author varchar,
  -- The distribution name
  dist varchar,
  -- The number of test failures for this release
  fail int,
  id INTEGER PRIMARY KEY NOT NULL,
  -- The number of NA results for this release, which means the release does not apply to the tester\'s machine due to OS, Perl version, or other conditions
  na int,
  -- The number of test passes for this release
  pass int,
  -- The number of unknown reports for this release
  unknown int,
  -- The distribution release version
  version varchar
);

CREATE INDEX pk_id08 ON releases (id);

--
-- Table: reports
--
CREATE TABLE reports (
-- Comments: 
-- CPAN Testers report
--

  _relational_id00 INTEGER PRIMARY KEY NOT NULL,
  comments ,
  created varchar,
  id varchar NOT NULL,
  distribution_id int NOT NULL,
  environment_id int NOT NULL,
  reporter_id int NOT NULL,
  result_id int NOT NULL,
  FOREIGN KEY (distribution_id) REFERENCES distributions(id),
  FOREIGN KEY (environment_id) REFERENCES environments(id),
  FOREIGN KEY (reporter_id) REFERENCES reporters(id),
  FOREIGN KEY (result_id) REFERENCES results(id)
);

CREATE INDEX pk__relational_id0002 ON reports (_relational_id00);

--
-- Table: report_summaries
--
CREATE TABLE report_summaries (
-- Comments: 
-- Flattened summary data from the test report data structure
--

  -- The date/time of the report in ISO8601 format
  date varchar,
  -- The name of the distribution tested
  dist varchar,
  -- The report grade. Pass is passing tests. Fail is failing tests. NA is the distribution cannot be used on the system. Unknown is any other problem.
  grade enum,
  -- The GUID of the full report this data came from
  guid varchar,
  id INTEGER PRIMARY KEY NOT NULL,
  -- The name of the operating system, like \'linux\', \'MSWin32\', \'darwin\'
  osname varchar,
  -- The version of the operating system, like \'4.8.0-2-amd64\'
  osvers varchar,
  -- The Perl version that ran the tests, like \'5.24.0\'
  perl varchar,
  -- The Perl platform that ran the tests, like \'x86_64-linux\'
  platform varchar,
  -- The name/email of the reporter who submitted this report
  reporter varchar,
  -- The version of the distribution tested
  version varchar
);

CREATE INDEX pk_id09 ON report_summaries (id);

--
-- Table: reporters
--
CREATE TABLE reporters (
  email varchar NOT NULL,
  id INTEGER PRIMARY KEY NOT NULL,
  name varchar
);

CREATE INDEX pk_id10 ON reporters (id);

--
-- Table: results
--
CREATE TABLE results (
  duration int,
  failures int,
  -- The report grade. Pass is passing tests. Fail is failing tests. NA is the distribution cannot be used on the system. Unknown is any other problem.
  grade enum NOT NULL,
  id INTEGER PRIMARY KEY NOT NULL,
  skipped int,
  tests int,
  warnings int,
  output_id int NOT NULL,
  todo_id int,
  FOREIGN KEY (output_id) REFERENCES test_outputs(id),
  FOREIGN KEY (todo_id) REFERENCES result_todoes(id)
);

CREATE INDEX pk_id11 ON results (id);

--
-- Table: result_todoes
--
CREATE TABLE result_todoes (
  fail int NOT NULL,
  id INTEGER PRIMARY KEY NOT NULL,
  pass int NOT NULL
);

CREATE INDEX pk_id12 ON result_todoes (id);

--
-- Table: systems
--
CREATE TABLE systems (
  cpu_count varchar,
  cpu_description varchar,
  cpu_type varchar,
  filesystem varchar,
  hostname varchar,
  id INTEGER PRIMARY KEY NOT NULL,
  osname varchar NOT NULL,
  osversion varchar
);

CREATE INDEX pk_id13 ON systems (id);

--
-- Table: system_variables
--
CREATE TABLE system_variables (
  id INTEGER PRIMARY KEY NOT NULL,
  key varchar,
  value varchar,
  variable_id int NOT NULL,
  FOREIGN KEY (variable_id) REFERENCES systems(id)
);

CREATE INDEX pk_id14 ON system_variables (id);

--
-- Table: test_outputs
--
CREATE TABLE test_outputs (
-- Comments: 
-- At least one of the properties must be set
--

  build varchar,
  configure varchar,
  id INTEGER PRIMARY KEY NOT NULL,
  install varchar,
  test varchar,
  uncategorized varchar
);

CREATE INDEX pk_id15 ON test_outputs (id);

--
-- Table: uploads
--
CREATE TABLE uploads (
-- Comments: 
-- A release to CPAN
--

  -- The CPAN ID of the author who released this version of this distribution
  author varchar,
  -- The distribution name
  dist varchar,
  -- The filename on PAUSE, without the author directory
  filename varchar,
  id INTEGER PRIMARY KEY NOT NULL,
  -- The date/time the file was released to CPAN, in UTC
  released datetime,
  -- The distribution release version
  version varchar
);

CREATE INDEX pk_id16 ON uploads (id);

COMMIT;'