--
-- Table: basic
--
DROP TABLE basic CASCADE;
CREATE TABLE basic (
  type_id integer NOT NULL,
  pack_code character(1) NOT NULL,
  length_bits integer NOT NULL,
  PRIMARY KEY (type_id)
);



--
-- Table: encoded_string
--
DROP TABLE encoded_string CASCADE;
CREATE TABLE encoded_string (
  type_id integer NOT NULL,
  base_type_id integer NOT NULL,
  encoding character varying(20) NOT NULL,
  PRIMARY KEY (type_id)
);



--
-- Table: enum
--
DROP TABLE enum CASCADE;
CREATE TABLE enum (
  type_id integer NOT NULL,
  base_type_id integer NOT NULL,
  PRIMARY KEY (type_id)
);



--
-- Table: structure_fields
--
DROP TABLE structure_fields CASCADE;
CREATE TABLE structure_fields (
  enum_id integer NOT NULL,
  value character varying(1024) NOT NULL,
  translation character varying(1024) NOT NULL,
  PRIMARY KEY (enum_id, value)
);



--
-- Table: fileformats
--
DROP TABLE fileformats CASCADE;
CREATE TABLE fileformats (
  type_id integer NOT NULL,
  base_type_id integer NOT NULL,
  PRIMARY KEY (type_id)
);



--
-- Table: recurring_count
--
DROP TABLE recurring_count CASCADE;
CREATE TABLE recurring_count (
  type_id integer NOT NULL,
  base_type_id integer NOT NULL,
  count integer NOT NULL,
  PRIMARY KEY (type_id)
);



--
-- Table: recurring_terminated
--
DROP TABLE recurring_terminated CASCADE;
CREATE TABLE recurring_terminated (
  type_id integer NOT NULL,
  base_type_id integer NOT NULL,
  terminator character varying(1024) NOT NULL,
  PRIMARY KEY (type_id)
);



--
-- Table: structure
--
DROP TABLE structure CASCADE;
CREATE TABLE structure (
  type_id integer NOT NULL,
  PRIMARY KEY (type_id)
);



--
-- Table: systems
--
DROP TABLE systems CASCADE;
CREATE TABLE systems (
  id serial NOT NULL,
  name character varying(255) NOT NULL,
  comments character varying(16384) NOT NULL,
  PRIMARY KEY (id)
);



--
-- Table: types
--
DROP TABLE types CASCADE;
CREATE TABLE types (
  id serial NOT NULL,
  system_id integer NOT NULL,
  name character varying(255) NOT NULL,
  comments character varying(16384) NOT NULL,
  PRIMARY KEY (id)
);

--
-- Foreign Key Definitions
--

ALTER TABLE encoded_string ADD FOREIGN KEY (base_type_id)
  REFERENCES types (id);

ALTER TABLE enum ADD FOREIGN KEY (base_type_id)
  REFERENCES types (id);

ALTER TABLE structure_fields ADD FOREIGN KEY (enum_id)
  REFERENCES types (id);

ALTER TABLE fileformats ADD FOREIGN KEY (base_type_id)
  REFERENCES types (id);

ALTER TABLE recurring_count ADD FOREIGN KEY (base_type_id)
  REFERENCES types (id);

ALTER TABLE recurring_terminated ADD FOREIGN KEY (base_type_id)
  REFERENCES types (id);

ALTER TABLE types ADD FOREIGN KEY (system_id)
  REFERENCES systems (id) ON DELETE CASCADE ON UPDATE CASCADE;
