[Date Prev] | [Thread Prev] | [Thread Next] | [Date Next] -- [Date Index] | [Thread Index] | [List Home]
Subject: Re: [cap-dev] CAP DB schema
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;
[Date Prev] | [Thread Prev] | [Thread Next] | [Date Next] -- [Date Index] | [Thread Index] | [List Home]