Re: [cap-dev] CAP DB schema

["Guillaume Radde (MSID)" <guillaume.radde@nist.gov>]

I attached an sql file that (if it works ;-)) should create all the
tables.

Guillaume

On Fri, 2008-07-25 at 13:41 -0400, Navom, Brandon W. wrote:
> Hello, does anyone have a script  (or other method) to create a
> Database schema in MySQL that will hold a CAP alert?
> 
>  
> 
> 
> 
> Thanks,
> 
> Brandon 
> 
-- 
Guillaume Radde
Guest Researcher
National Institute of Standards and Technology
(301) 975-4745
--
-- Create schema capexercise
--

CREATE DATABASE IF NOT EXISTS capexercise;
USE capexercise;

--
-- Definition of table `agency`
--



DROP TABLE IF EXISTS `alert`;
CREATE TABLE `alert` (
  `alert_id` int(10) unsigned NOT NULL auto_increment,
  `identifier` varchar(45) NOT NULL,
  `sender` text NOT NULL,
  `sent` datetime NOT NULL,
  `status` enum('Actual','Exercise','System','Test','Draft','Specific_Term') NOT NULL,
  `msgType` enum('Alert','Update','Cancel','Ack','Error','Specific_Term') NOT NULL,
  `source` varchar(45) default NULL,
  `scope` enum('Public','Restricted','Private','Specific_Term') NOT NULL,
  `restriction` text,
  `addresses` text,
  `note` text,
  `references` text,
  `incidents` text,
  PRIMARY KEY  (`alert_id`)
) ENGINE=InnoDB AUTO_INCREMENT=11211 DEFAULT CHARSET=latin1;


--
-- Definition of table `area`
--

DROP TABLE IF EXISTS `area`;
CREATE TABLE `area` (
  `area_id` int(10) unsigned NOT NULL auto_increment,
  `area_desc` text NOT NULL,
  `altitude` varchar(45) default NULL,
  `ceiling` varchar(45) default NULL,
  `info_id` int(10) unsigned NOT NULL,
  PRIMARY KEY  (`area_id`),
  KEY `FK_area_1` (`info_id`),
  CONSTRAINT `FK_area_1` FOREIGN KEY (`info_id`) REFERENCES `info` (`info_id`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=latin1;





--
-- Definition of table `circle`
--

DROP TABLE IF EXISTS `circle`;
CREATE TABLE `circle` (
  `circle_id` int(10) unsigned NOT NULL auto_increment,
  `area_id` int(10) unsigned NOT NULL,
  `circle` varchar(45) NOT NULL,
  PRIMARY KEY  (`circle_id`),
  KEY `FK_circle_1` (`area_id`),
  CONSTRAINT `FK_circle_1` FOREIGN KEY (`area_id`) REFERENCES `area` (`area_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;




--
-- Definition of table `event_category`
--

DROP TABLE IF EXISTS `event_category`;
CREATE TABLE `event_category` (
  `event_category_id` int(10) unsigned NOT NULL auto_increment,
  `info_id` int(10) unsigned NOT NULL,
  `category` enum('Geo','Met','Safety','Security','Rescue','Fire','Health','Env','Transport','Infra','CBRNE','Other') NOT NULL,
  PRIMARY KEY  (`event_category_id`),
  KEY `FK_event_category_1` (`info_id`),
  CONSTRAINT `FK_event_category_1` FOREIGN KEY (`info_id`) REFERENCES `info` (`info_id`)
) ENGINE=InnoDB AUTO_INCREMENT=9571 DEFAULT CHARSET=latin1;


--
-- Definition of table `event_code`
--

DROP TABLE IF EXISTS `event_code`;
CREATE TABLE `event_code` (
  `event_code_id` int(10) unsigned NOT NULL auto_increment,
  `info_id` int(10) unsigned NOT NULL,
  `value_name` varchar(45) NOT NULL,
  `value` varchar(45) NOT NULL,
  PRIMARY KEY  (`event_code_id`),
  KEY `FK_event_code_1` (`info_id`),
  CONSTRAINT `FK_event_code_1` FOREIGN KEY (`info_id`) REFERENCES `info` (`info_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;


DROP TABLE IF EXISTS `geocode`;
CREATE TABLE `geocode` (
  `geocode_id` int(10) unsigned NOT NULL auto_increment,
  `area_id` int(10) unsigned NOT NULL,
  `value_name` varchar(45) NOT NULL,
  `value` varchar(45) NOT NULL,
  PRIMARY KEY  (`geocode_id`),
  KEY `FK_geocode_1` (`area_id`),
  CONSTRAINT `FK_geocode_1` FOREIGN KEY (`area_id`) REFERENCES `area` (`area_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;


--
-- Definition of table `handling_code`
--

DROP TABLE IF EXISTS `handling_code`;
CREATE TABLE `handling_code` (
  `handling_code_id` int(10) unsigned NOT NULL auto_increment,
  `alert_id` int(10) unsigned NOT NULL,
  `code` varchar(45) NOT NULL,
  PRIMARY KEY  (`handling_code_id`),
  KEY `FK_handling_code_1` (`alert_id`),
  CONSTRAINT `FK_handling_code_1` FOREIGN KEY (`alert_id`) REFERENCES `alert` (`alert_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


--
-- Definition of table `info`
--

DROP TABLE IF EXISTS `info`;
CREATE TABLE `info` (
  `info_id` int(10) unsigned NOT NULL auto_increment,
  `language` text,
  `event` text,
  `urgency` enum('Immediate','Expected','Future','Past','Unknown','Specific_Term') NOT NULL,
  `severity` enum('Extreme','Severe','Moderate','Minor','Unknown','Specific_Term') NOT NULL,
  `certainty` enum('Observed','Likely','Possible','Unlikely','Unknown','Specific_Term') NOT NULL,
  `audience` text,
  `effective` datetime default NULL,
  `onset` datetime default NULL,
  `expires` datetime default NULL,
  `senderName` text,
  `headline` text,
  `description` text,
  `instruction` text,
  `web` text,
  `contact` text,
  `alert_id` int(10) unsigned NOT NULL,
  PRIMARY KEY  (`info_id`),
  KEY `FK_info_1` (`alert_id`),
  CONSTRAINT `FK_info_1` FOREIGN KEY (`alert_id`) REFERENCES `alert` (`alert_id`)
) ENGINE=InnoDB AUTO_INCREMENT=9611 DEFAULT CHARSET=latin1;


--
-- Definition of table `parameter`
--

DROP TABLE IF EXISTS `parameter`;
CREATE TABLE `parameter` (
  `parameter_id` int(10) unsigned NOT NULL auto_increment,
  `info_id` int(10) unsigned NOT NULL,
  `value_name` varchar(45) NOT NULL,
  `value` varchar(45) NOT NULL,
  PRIMARY KEY  (`parameter_id`),
  KEY `FK_parameter_1` (`info_id`),
  CONSTRAINT `FK_parameter_1` FOREIGN KEY (`info_id`) REFERENCES `info` (`info_id`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=latin1;



--
-- Definition of table `polygon`
--

DROP TABLE IF EXISTS `polygon`;
CREATE TABLE `polygon` (
  `polygon_id` int(10) unsigned NOT NULL auto_increment,
  `area_id` int(10) unsigned NOT NULL,
  `polygon` varchar(45) NOT NULL,
  PRIMARY KEY  (`polygon_id`),
  KEY `FK_polygon_1` (`area_id`),
  CONSTRAINT `FK_polygon_1` FOREIGN KEY (`area_id`) REFERENCES `area` (`area_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;



--
-- Definition of table `resource`
--

DROP TABLE IF EXISTS `resource`;
CREATE TABLE `resource` (
  `resource_id` int(10) unsigned NOT NULL auto_increment,
  `resource_desc` text NOT NULL,
  `mime_type` text,
  `size` int(10) unsigned default NULL,
  `uri` text,
  `deref_uri` text,
  `digest` text,
  `info_id` int(10) unsigned NOT NULL,
  PRIMARY KEY  (`resource_id`),
  KEY `FK_resource_1` (`info_id`),
  CONSTRAINT `FK_resource_1` FOREIGN KEY (`info_id`) REFERENCES `info` (`info_id`)
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=latin1;


--
-- Definition of table `response_type`
--

DROP TABLE IF EXISTS `response_type`;
CREATE TABLE `response_type` (
  `response_type_id` int(10) unsigned NOT NULL auto_increment,
  `info_id` int(10) unsigned NOT NULL,
  `response_type` enum('Shelter','Evacuate','Prepare','Execute','Monitor','Assess','None','Specific_Term') NOT NULL,
  PRIMARY KEY  (`response_type_id`),
  KEY `FK_response_type_1` (`info_id`),
  CONSTRAINT `FK_response_type_1` FOREIGN KEY (`info_id`) REFERENCES `info` (`info_id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;