-- 
-- Created by SQL::Translator::Producer::MySQL
-- Created on Sat Nov 15 12:47:05 2008
-- 
SET foreign_key_checks=0;

DROP TABLE IF EXISTS `basic`;
--
-- Table: `basic`
--
CREATE TABLE `basic` (
  `type_id` integer NOT NULL,
  `pack_code` char(1) NOT NULL,
  `length_bits` integer NOT NULL,
  PRIMARY KEY (`type_id`)
);

DROP TABLE IF EXISTS `encoded_string`;
--
-- Table: `encoded_string`
--
CREATE TABLE `encoded_string` (
  `type_id` integer NOT NULL,
  `base_type_id` integer NOT NULL,
  `encoding` varchar(20) NOT NULL,
  INDEX encoded_string_idx_base_type_id (`base_type_id`),
  PRIMARY KEY (`type_id`),
  CONSTRAINT `encoded_string_fk_base_type_id` FOREIGN KEY (`base_type_id`) REFERENCES `types` (`id`)
) ENGINE=InnoDB;

DROP TABLE IF EXISTS `enum`;
--
-- Table: `enum`
--
CREATE TABLE `enum` (
  `type_id` integer NOT NULL,
  `base_type_id` integer NOT NULL,
  INDEX enum_idx_base_type_id (`base_type_id`),
  PRIMARY KEY (`type_id`),
  CONSTRAINT `enum_fk_base_type_id` FOREIGN KEY (`base_type_id`) REFERENCES `types` (`id`)
) ENGINE=InnoDB;

DROP TABLE IF EXISTS `enum_values`;
--
-- Table: `enum_values`
--
CREATE TABLE `enum_values` (
  `enum_id` integer NOT NULL,
  `value` text NOT NULL,
  `translation` text NOT NULL,
  INDEX enum_values_idx_enum_id (`enum_id`),
  PRIMARY KEY (`enum_id`, `value`),
  CONSTRAINT `enum_values_fk_enum_id` FOREIGN KEY (`enum_id`) REFERENCES `types` (`id`)
) ENGINE=InnoDB;

DROP TABLE IF EXISTS `fileformats`;
--
-- Table: `fileformats`
--
CREATE TABLE `fileformats` (
  `type_id` integer NOT NULL,
  `base_type_id` integer NOT NULL,
  INDEX fileformats_idx_base_type_id (`base_type_id`),
  PRIMARY KEY (`type_id`),
  CONSTRAINT `fileformats_fk_base_type_id` FOREIGN KEY (`base_type_id`) REFERENCES `types` (`id`)
) ENGINE=InnoDB;

DROP TABLE IF EXISTS `recurring_count`;
--
-- Table: `recurring_count`
--
CREATE TABLE `recurring_count` (
  `type_id` integer NOT NULL,
  `base_type_id` integer NOT NULL,
  `count` integer NOT NULL,
  INDEX recurring_count_idx_base_type_id (`base_type_id`),
  PRIMARY KEY (`type_id`),
  CONSTRAINT `recurring_count_fk_base_type_id` FOREIGN KEY (`base_type_id`) REFERENCES `types` (`id`)
) ENGINE=InnoDB;

DROP TABLE IF EXISTS `recurring_terminated`;
--
-- Table: `recurring_terminated`
--
CREATE TABLE `recurring_terminated` (
  `type_id` integer NOT NULL,
  `base_type_id` integer NOT NULL,
  `terminator` text NOT NULL,
  INDEX recurring_terminated_idx_base_type_id (`base_type_id`),
  PRIMARY KEY (`type_id`),
  CONSTRAINT `recurring_terminated_fk_base_type_id` FOREIGN KEY (`base_type_id`) REFERENCES `types` (`id`)
) ENGINE=InnoDB;

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

DROP TABLE IF EXISTS `structure_fields`;
--
-- Table: `structure_fields`
--
CREATE TABLE `structure_fields` (
  `struct_id` integer NOT NULL,
  `field_id` integer NOT NULL,
  `name` varchar(255) NOT NULL,
  `position` integer NOT NULL,
  `type_id` integer NOT NULL,
  `value` text NOT NULL,
  `comments` text NOT NULL,
  INDEX structure_fields_idx_struct_id (`struct_id`),
  INDEX structure_fields_idx_type_id (`type_id`),
  PRIMARY KEY (`struct_id`, `field_id`),
  CONSTRAINT `structure_fields_fk_struct_id` FOREIGN KEY (`struct_id`) REFERENCES `types` (`id`),
  CONSTRAINT `structure_fields_fk_type_id` FOREIGN KEY (`type_id`) REFERENCES `types` (`id`)
) ENGINE=InnoDB;

DROP TABLE IF EXISTS `systems`;
--
-- Table: `systems`
--
CREATE TABLE `systems` (
  `id` integer NOT NULL auto_increment,
  `name` varchar(255) NOT NULL,
  `comments` text NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

DROP TABLE IF EXISTS `types`;
--
-- Table: `types`
--
CREATE TABLE `types` (
  `id` integer NOT NULL auto_increment,
  `system_id` integer NOT NULL,
  `name` varchar(255) NOT NULL,
  `comments` text,
  INDEX types_idx_system_id (`system_id`),
  PRIMARY KEY (`id`),
  CONSTRAINT `types_fk_system_id` FOREIGN KEY (`system_id`) REFERENCES `systems` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB;

SET foreign_key_checks=1;