-- 
-- Created by SQL::Translator::Producer::MySQL
-- Created on Sun Nov  9 13:32:18 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 (`base_type_id`),
  PRIMARY KEY (`type_id`),
  CONSTRAINT `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 (`base_type_id`),
  PRIMARY KEY (`type_id`),
  CONSTRAINT `fk_base_type_id_1` FOREIGN KEY (`base_type_id`) REFERENCES `types` (`id`)
) ENGINE=InnoDB;

DROP TABLE IF EXISTS `structure_fields`;
--
-- Table: `structure_fields`
--
CREATE TABLE `structure_fields` (
  `enum_id` integer NOT NULL,
  `value` text NOT NULL,
  `translation` text NOT NULL,
  INDEX (`enum_id`),
  PRIMARY KEY (`enum_id`, `value`),
  CONSTRAINT `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 (`base_type_id`),
  PRIMARY KEY (`type_id`),
  CONSTRAINT `fk_base_type_id_2` 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 (`base_type_id`),
  PRIMARY KEY (`type_id`),
  CONSTRAINT `fk_base_type_id_3` 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 (`base_type_id`),
  PRIMARY KEY (`type_id`),
  CONSTRAINT `fk_base_type_id_4` 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 `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 NOT NULL,
  INDEX (`system_id`),
  PRIMARY KEY (`id`),
  CONSTRAINT `fk_system_id` FOREIGN KEY (`system_id`) REFERENCES `systems` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB;

SET foreign_key_checks=1;