Data model

SET client_encoding = 'SQL_ASCII';
SET check_function_bodies = false;
SET client_min_messages = warning;

SET search_path = public, pg_catalog;

SET default_tablespace = '';

SET default_with_oids = true;

-- Also requires /web/generic/sql/data-model/create-users.sql

CREATE TABLE performdb_model_types (
  type_id serial primary key,
  short_name varchar(32) unique not null,
  name varchar(255) not null,
  mime_type varchar(255) not null,
  validation_func varchar(255),
  link varchar(255),
  description text not null,
  added_by varchar(32) references users(short_name) not null,
  on_behalf_of varchar(32) references users(short_name),
  added_at timestamp not null default now(),
  last_edit_by varchar(32) references users(short_name),
  last_edit_at timestamp
);

CREATE TABLE performdb_models (
  model_id serial primary key,
  short_name varchar(32) not null,
  name varchar(255) not null,
  def text not null,
  description text,
  "type" integer references performdb_model_types(type_id) not null,
  added_by varchar(32) references users(short_name) not null,
  on_behalf_of varchar(32) references users(short_name),
  added_at timestamp not null default now(),
  last_edit_by varchar(32) references users(short_name),
  last_edit_at timestamp
);

CREATE TABLE performdb_keywords (
  keyword_id serial primary key,
  short_name varchar(32) unique not null,
  name varchar(255) unique not null,
  description text,
  added_by varchar(32) references users(short_name) not null,
  on_behalf_of varchar(32) references users(short_name),
  added_at timestamp not null default now(),
  last_edit_by varchar(32) references users(short_name),
  last_edit_at timestamp,
  for_all_revisions boolean not null
);

CREATE TABLE performdb_keyword_model_map (
  on_model integer references performdb_models not null,
  keyword integer references performdb_keywords not null,
  added_by varchar(32) references users(short_name) not null,
  on_behalf_of varchar(32) references users(short_name),
  added_at timestamp not null default now(),
  last_edit_by varchar(32) references users(short_name),
  last_edit_at timestamp
);

CREATE TABLE performdb_measure_types (
  type_id serial primary key,
  short_name varchar(32) unique not null,
  name varchar(255) not null,
  mime_type varchar(255) not null,
  validation_func varchar(255),
  link varchar(255),
  description text not null,
  added_by varchar(32) references users(short_name) not null,
  on_behalf_of varchar(32) references users(short_name),
  added_at timestamp not null default now(),
  last_edit_by varchar(32) references users(short_name),
  last_edit_at timestamp
);

CREATE TABLE performdb_measures (
  measure_id serial primary key,
  measure_type integer references performdb_measure_types(type_id) not null,
  name varchar(255),
  def text not null,
  added_by varchar(32) references users(short_name) not null,
  on_behalf_of varchar(32) references users(short_name),
  added_at timestamp not null default now(),
  last_edit_by varchar(32) references users(short_name),
  last_edit_at timestamp,
  title varchar(255),
  for_model integer references performdb_models(model_id)
);

CREATE TABLE performdb_param_types (
  type_id serial primary key,
  short_name varchar(32) unique not null,
  name varchar(255) not null,
  mime_type varchar(255) not null,
  link varchar(255),
  description text not null,
  added_by varchar(32) references users(short_name) not null,
  on_behalf_of varchar(32) references users(short_name),
  added_at timestamp not null default now(),
  last_edit_by varchar(32) references users(short_name),
  last_edit_at timestamp
);

CREATE TABLE performdb_params (
  param_id serial primary key,
  param_type integer references performdb_param_types(type_id) not null,
  data text not null,
  added_by varchar(32) references users(short_name) not null,
  on_behalf_of varchar(32) references users(short_name),
  added_at timestamp not null default now(),
  last_edit_by varchar(32) references users(short_name),
  last_edit_at timestamp
);

CREATE TABLE performdb_tools (
  short_name varchar(32) unique not null,
  name varchar(255),
  link varchar(255),
  description text,
-- maybe logo later
  added_by varchar(32) references users(short_name) not null,
  on_behalf_of varchar(32) references users(short_name),
  added_at timestamp not null default now(),
  last_edit_by varchar(32) references users(short_name),
  last_edit_at timestamp
);

CREATE TABLE performdb_result_formats (
  format_id serial primary key,
  short_name varchar(32) unique not null,
  name varchar(255) not null,
  mime_type varchar(255) not null,
  link varchar(255),
  description text not null,
  added_by varchar(32) references users(short_name) not null,
  on_behalf_of varchar(32) references users(short_name),
  added_at timestamp not null default now(),
  last_edit_by varchar(32) references users(short_name),
  last_edit_at timestamp
);

CREATE TABLE performdb_result_types (
  type_id serial primary key,
  short_name varchar(32) unique not null,
  name varchar(255) not null,
  link varchar(255),
  description text not null,
  added_by varchar(32) references users(short_name) not null,
  on_behalf_of varchar(32) references users(short_name),
  added_at timestamp not null default now(),
  last_edit_by varchar(32) references users(short_name),
  last_edit_at timestamp
);

CREATE TABLE performdb_result_outputs (
 output_id serial primary key,
 short_name varchar(32) not null check is not in ('raw'),
 for_type integer references performdb_result_types,
 mime_type varchar(255),
 name varchar(32) not null,
 default_x_label varchar(255),
 default_y_label varchar(255),
 constraint performdb_resultoutputtype_shortname_uniq unique(short_name,for_type),
 constraint performdb_resultoutputtype_name_uniq unique(name,for_type)
);

CREATE UNIQUE INDEX performdb_resultoutputtype_globals_uniq on performdb_result_outputs(short_name) where for_type is null;

-- INSERT INTO performdb_result_outputs (short_name,name,for_type) VALUES ('bot','PerformDB bot');

COPY performdb_result_outputs (short_name, for_type, mime_type) FROM stdin;
pepa	PEPA model	text/plain	\N	http://www.dcs.ed.ac.uk/pepa/	Performance Evaluation Process Algebra	bot
raw			
svg	svg
png

CREATE TABLE performdb_results (
  result_id serial primary key,
  result_format integer references performdb_result_formats(format_id) not null,
  result_type integer references performdb_result_types(type_id) not null,
  for_model integer references performdb_models(model_id) not null,
  for_measure integer references performdb_measures(measure_id) not null,
  with_params integer references performdb_params(param_id),
  data text not null,
  tool varchar(32) references performdb_tools(short_name),
  tool_run timestamp,
  tool_command_line text,
  tool_version varchar(255),
  added_by varchar(32) references users(short_name) not null,
  on_behalf_of varchar(32) references users(short_name),
  added_at timestamp not null default now(),
  last_edit_by varchar(32) references users(short_name),
  last_edit_at timestamp,
  description text,
  title varchar(255)
);

CREATE TABLE performdb_pending_results (
  pending_id serial primary key,
  result_format integer references performdb_result_formats(format_id) not null,
  result_type integer references performdb_result_types(type_id) not null,
  for_model integer references performdb_models(model_id) not null,
  for_measure integer references performdb_measures(measure_id) not null,
  with_params integer references performdb_params(param_id),
  tool varchar(32) references performdb_tools(short_name),
  eta timestamp,
  added_by varchar(32) references users(short_name) not null,
  on_behalf_of varchar(32) references users(short_name),
  added_at timestamp not null default now(),
  last_edit_by varchar(32) references users(short_name),
  last_edit_at timestamp
);

CREATE TABLE performdb_model_relation_types (
  type_id serial primary key,
  short_name varchar(32) not null unique,
  name varchar(255) not null unique,
  added_by varchar(32) references users(short_name) not null,
  on_behalf_of varchar(32) references users(short_name),
  added_at timestamp not null default now(),
  last_edit_by varchar(32) references users(short_name),
  last_edit_at timestamp,
  for_all_revisions boolean not null
);

CREATE TABLE performdb_model_relations (
  relation_id serial primary key,
  from_model integer references performdb_models(model_id) not null,
  to_model integer references performdb_models(model_id) not null,
  relationship integer references performdb_model_relation_types(type_id) not null,
  added_by varchar(32) references users(short_name) not null,
  on_behalf_of varchar(32) references users(short_name),
  added_at timestamp not null default now(),
  last_edit_by varchar(32) references users(short_name),
  last_edit_at timestamp
);

CREATE TABLE performdb_result_relation_types (
  type_id serial primary key,
  short_name varchar(32) not null unique,
  name varchar(255) not null unique,
  added_by varchar(32) references users(short_name) not null,
  on_behalf_of varchar(32) references users(short_name),
  added_at timestamp not null default now(),
  last_edit_by varchar(32) references users(short_name),
  last_edit_at timestamp
);

CREATE TABLE performdb_result_relations (
  relation_id serial primary key,
  from_result integer references performdb_results(result_id) not null,
  to_result integer references performdb_results(result_id) not null,
  relationship integer references performdb_result_relation_types(type_id) not null,
  added_by varchar(32) references users(short_name) not null,
  on_behalf_of varchar(32) references users(short_name),
  added_at timestamp not null default now(),
  last_edit_by varchar(32) references users(short_name),
  last_edit_at timestamp
);

CREATE TABLE performdb_measure_relation_types (
  type_id serial primary key,
  short_name varchar(32) not null unique,
  name varchar(255) not null unique,
  added_by varchar(32) references users(short_name) not null,
  on_behalf_of varchar(32) references users(short_name),
  added_at timestamp not null default now(),
  last_edit_by varchar(32) references users(short_name),
  last_edit_at timestamp
);

CREATE TABLE performdb_measure_relations (
  relation_id serial primary key,
  from_measure integer references performdb_measures(measure_id) not null,
  to_measure integer references performdb_measures(measure_id) not null,
  relationship integer references performdb_measure_relation_types(type_id) not null,
  added_by varchar(32) references users(short_name) not null,
  on_behalf_of varchar(32) references users(short_name),
  added_at timestamp not null default now(),
  last_edit_by varchar(32) references users(short_name),
  last_edit_at timestamp
);

CREATE TABLE performdb_query_modules (
  classifier_id serial primary key,
  func varchar(80) not null,
  mount_point varchar(80) not null,
  mount_pattern text,
  added_by varchar(32) references users(short_name) not null,
  on_behalf_of varchar(32) references users(short_name),
  added_at timestamp not null default now(),
  last_edit_by varchar(32) references users(short_name),
  last_edit_at timestamp
);

CREATE TABLE performdb_classifiers (
  classifier_id serial primary key,
  short_name varchar(32) unique not null,
  name varchar(255) not null,
  description text,
  def_func varchar(80) not null,
  def_exec varchar(80) not null,
  added_by varchar(32) references users(short_name) not null,
  on_behalf_of varchar(32) references users(short_name),
  added_at timestamp not null default now(),
  last_edit_by varchar(32) references users(short_name),
  last_edit_at timestamp
);

ALTER TABLE performdb_classifiers 
ADD CONSTRAINT performdb_classifier_def CHECK 
(  ((def_func IS NOT NULL) AND (def_exec IS NULL)) 
OR ((def_func IS NULL) AND (def_exec IS NOT NULL)));


CREATE TABLE performdb_model_converters (
  converter_id serial primary key,
  short_name varchar(32) unique not null,
  name varchar(255) not null,
  description text,
  func varchar(80) not null,
  input_type integer references performdb_model_types not null,
  return_type integer references performdb_model_types,
  direct_return_type varchar(255),
  added_by varchar(32) references users(short_name) not null,
  on_behalf_of varchar(32) references users(short_name),
  added_at timestamp not null default now(),
  last_edit_by varchar(32) references users(short_name),
  last_edit_at timestamp
);

ALTER TABLE performdb_model_converters
ADD CONSTRAINT performdb_modconv_return CHECK 
(  ((return_type IS NOT NULL) AND (direct_return_type IS NULL)) 
OR ((return_type IS NULL) AND (direct_return_type IS NOT NULL)));

CREATE TABLE performdb_measure_converters (
  converter_id serial primary key,
  short_name varchar(32) unique not null,
  name varchar(255) not null,
  description text,
  func varchar(80) not null,
  input_type integer references performdb_measure_types not null,
  return_type integer references performdb_measure_types not null,
  direct_return_type varchar(255),
  added_by varchar(32) references users(short_name) not null,
  on_behalf_of varchar(32) references users(short_name),
  added_at timestamp not null default now(),
  last_edit_by varchar(32) references users(short_name),
  last_edit_at timestamp
);

ALTER TABLE performdb_measure_converters
ADD CONSTRAINT performdb_measconv_return CHECK 
(  ((return_type IS NOT NULL) AND (direct_return_type IS NULL)) 
OR ((return_type IS NULL) AND (direct_return_type IS NOT NULL)));

CREATE TABLE performdb_result_format_converters (
  converter_id serial primary key,
  short_name varchar(32) unique not null,
  name varchar(255) not null,
  description text,
  func varchar(80) not null,
  input_format integer references performdb_result_formats not null,
  return_format integer references performdb_result_formats not null,
  direct_return_type varchar(255),
  added_by varchar(32) references users(short_name) not null,
  on_behalf_of varchar(32) references users(short_name),
  added_at timestamp not null default now(),
  last_edit_by varchar(32) references users(short_name),
  last_edit_at timestamp
);

ALTER TABLE performdb_result_format_converters
ADD CONSTRAINT performdb_resultformatconv_return CHECK 
(  ((return_format IS NOT NULL) AND (direct_return_type IS NULL)) 
OR ((return_format IS NULL) AND (direct_return_type IS NOT NULL)));

INSERT INTO users (short_name,display_name) VALUES ('bot','PerformDB bot');

COPY performdb_model_types (short_name, name, mime_type, validation_func, link, description,added_by) FROM stdin;
pepa	PEPA model	text/plain	\N	http://www.dcs.ed.ac.uk/pepa/	Performance Evaluation Process Algebra	bot
pepa-queue	PEPA queue	text/plain	\N	http://aesop.doc.ic.ac.uk/pubs/pepa-queues-intro/	PEPA queues, embedding PEPA components in queueing networks.	bot
pepa-net	PEPA Net	text/plain	\N		PEPA nets, embedding PEPA components in a Petri-net.	bot
pnml	PNML model	text/plain	\N		Petri-net Markup Language.	bot
dnamaca	DNAmaca model	text/plain	\N		Petri-net model for DNAmaca.	bot
\.

CREATE OR REPLACE VIEW performdb_model_revisions AS 
(SELECT m.model_id, count(m2.model_id) as revision
 FROM performdb_models m 
 JOIN performdb_models m2 ON m.short_name=m2.short_name 
 WHERE m.model_id>=m2.model_id 
 GROUP BY m.model_id);