use mcs_lead; -- --------------------------------------------------------- -- myLEAD Version 0.3 Alpha -- Server version 5.0.3 -- Indiana University -- Distributed Data Everywhere -- Based on MCS schema -- --------------------------------------------------------- -- ******************************************* -- * Definitions * -- ******************************************* -- -- Table structure for table lead_attribute_definition -- DROP TABLE IF EXISTS lead_attribute_definition ; CREATE TABLE lead_attribute_definition ( Attribute_id int(11) NOT NULL auto_increment, Creator_id int(11) NOT NULL default 0, Attribute_name varchar(100) NOT NULL default '', Attribute_description varchar(255) NOT NULL default '', Attribute_short_desc varchar(30) NOT NULL default '', Attribute_type int(11) NOT NULL default 1, Top_attr_id int(11) NOT NULL default 0, Parent_sequence varchar(100) NOT NULL default '', Parent_id int(11) NOT NULL default 0, SubAttributes int(11) NOT NULL default 0, PRIMARY KEY ( Attribute_id ), UNIQUE KEY Attribute_name ( Attribute_name ), KEY Creator_id ( Creator_id ) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; -- -- Loading attribute definitions for table: lead_attribute_definition -- /*!40000 ALTER TABLE lead_attribute_definition DISABLE KEYS */; LOCK TABLES lead_attribute_definition WRITE; INSERT INTO lead_attribute_definition ( Attribute_id , Creator_id , Attribute_name , Attribute_description , Attribute_short_desc , Attribute_type , Top_attr_id , Parent_sequence , Parent_id , SubAttributes ) VALUES (1,0,'DataSystem','Collection - generation system','Data System',1,1,'1',0,0),(2,0,'DBT','Dry Bulb Temperature','Dry Bulb Temperature',1,2,'2',0,0),(3,0,'DewPt','Dew Point','Dew Point',1,3,'3',0,0),(4,0,'WBT','Wet Bulb Temperature','Wet Bulb Temperature',1,4,'4',0,0),(5,0,'VertWind','Vertical Wind','Vertical Wind',1,5,'5',0,0),(6,0,'ZonWind','Zonal Wind - an East West wind','Zonal Wind',1,6,'6',0,0),(7,0,'MerWind','Meridional Wind','Meridional Wind',1,7,'7',0,0),(8,0,'WindSpd','Wind Speed','Wind Speed',1,8,'8',0,0),(9,0,'WindDir','Wind Direction','Wind Direction',1,9,'9',0,0),(10,0,'GeoPoHt','Geopotential Height','Geopotential Height',1,10,'10',0,0),(11,0,'RelHunid','Relative Humidity','Relative Humidity',1,11,'11',0,0),(12,0,'PresTend','Pressure Tendency','Pressure Tendency',1,12,'12',0,0),(13,0,'MSLP','Mean Sea Level Pressure','Mean Sea Level Pressure',1,13,'13',0,0),(14,0,'Altimeter','Altimeter Setting','Altimeter Setting',1,14,'14',0,0),(15,0,'AVV','Absolute Vertical Vorticity','Absolute Vertical Vorticity',1,15,'15',0,0),(16,0,'RVV','Relative Vertical Vorticity','Relative Vertical Vorticity',1,16,'16',0,0),(17,0,'GeoPoHtTend','Geopotential Height Tendency','Geopotential Height Tendency',1,17,'17',0,0),(18,0,'AbsHunid','Absolute Humidity','Absolute Humidity',1,18,'18',0,0),(19,0,'SpecHunid','Specific Humidity','Specific Humidity',1,19,'19',0,0),(20,0,'MixRatio','Mixing Ratio','Mixing Ratio',1,20,'20',0,0),(21,0,'HeatFlux','Heat Flux','Heat Flux',1,21,'21',0,0),(22,0,'MoFlux','Momentum Flux','Momentum Flux',1,22,'22',0,0),(23,0,'MoistFlux','Moisture Flux','Moisture Flux',1,23,'23',0,0),(24,0,'Reflect','Reflectivity','Reflectivity',1,24,'24',0,0),(25,0,'RadVelocity','Radial Velocity','Radial Velocity',1,25,'25',0,0),(26,0,'SpectrumWidth','Spectrum Width','Spectrum Width',1,26,'26',0,0),(27,0,'AccumPrecip','Accumulated Precipitation','Accumulated Precipitation',1,27,'27',0,0),(28,0,'CTT','Cloud Top Temperature','Cloud Top Temperature',1,28,'28',0,0),(29,0,'SurfAlbedo','Surface Albedo','Surface Albedo',1,29,'29',0,0),(30,0,'SST','Surface Skin Temperature','Surface Skin Temperature',1,30,'30',0,0),(31,0,'WST','Water Surface Temperature','Water Surface Temperature',1,31,'31',0,0),(32,0,'PrecipWater','Precipitable Water','Precipitable Water',1,32,'32',0,0),(33,0,'Dimensionality','Dimensionality','Dimensionality',1,33,'33',0,0),(34,0,'GeoCov','Geographic Coverage','Geographic Coverage',1,34,'34',0,0),(35,0,'GeoCRS','Geographic Coverage Horizontal Coordinate Reference System','Horizontal Coordinate Referenc',1,34,'34.35',34,1),(36,0,'GeoVertCRS','Geographic Coverage Vertical Coordinate System','Vertical Coordinate System',1,34,'34.36',34,0),(37,0,'GeoHorzGCS','Geographic Coverage Horizontal Geographic Coordinate System','Horizontal Geographic Coordina',1,34,'34.35.37',35,0),(38,0,'GeoHorzPCT','Geographic Coverage Horizontal Planar Coordinate Type','Horizontal Planar Coordinate T',1,34,'34.35.38',35,0),(39,0,'GeoHorzPCInfo','Geographic Coverage Horizontal Planar Coordinate Information','Planar Coordinate Information',1,34,'34.35.38.39',38,1),(40,0,'PlanarDBR','Geographic Coverage Horizontal Planar Coordinate Information - Distance Bearing Representation','Planar Distance Bearing Repres',1,34,'34.35.38.39.40',39,0),(41,0,'PlanarCR','Geographic Coverage Horizontal Planar Coordinate Information - Coordinate Representation','Planar Coordinate Representati',1,34,'34.35.38.39.41',39,0),(42,0,'GeoHorzPCS','Geographic Coverage Horizontal Planar Coordinate System','Planar Coordinate System',1,34,'34.35.38.42',38,1),(43,0,'GeoHorzPCSLocal','Geographic Coverage Horizontal Planar Coordinate System - Local','Planar Coordinate System - Loc',1,34,'34.35.38.42.43',42,0),(44,0,'GeoHorzPCSGrid','Geographic Coverage Horizontal Planar Coordinate System - Grid','Planar Coordinate System - Gri',1,34,'34.35.38.42.44',42,0),(45,0,'GeoHorzPCSMap','Geographic Coverage Horizontal Planar Coordinate System - Map Projection','Planar Coordinate System - Map',1,34,'34.35.38.42.45',42,0),(46,0,'GeoHorzLCS','Geographic Coverage Horizontal Local Coordinate System','Horizontal Local Coordinate Sy',1,34,'34.35.46',35,0),(47,0,'GeoHorzGM','Geographic Coverage Horizontal Geodetic Model Coordinate System','Horizontal Geodetic Model',1,34,'34.35.47',35,0),(48,0,'TemporalCov','Temporal Coverage','Temporal Coverage',1,48,'48',0,0),(49,0,'Avail','Availability','Availability',1,49,'49',0,0),(50,0,'Format','Data Format','Data Format',1,50,'50',0,0),(51,0,'AccessCon','Access Constraints','Access Constraints',1,51,'51',0,0),(52,0,'UseCon','Usage Constraints','Usage Constraints',1,52,'52',0,0),(53,0,'OrgSource','Organizational Source','Organizational Source',1,53,'53',0,0),(54,0,'QA','Quality Assurance','Quality Assurance',1,54,'54',0,0),(55,0,'ContactInfo','Data Provider Contact Info','Provider Contact Info',1,55,'55',0,0),(56,0,'ContactAddr','Data Provider Contact Address','Provider Contact Address',1,55,'55.56',55,0),(57,0,'Discipline','Discipline','Discipline',1,57,'57',0,0),(58,0,'Topic','Topic','Topic',1,58,'58',0,0),(59,0,'Term','Term','Term',1,59,'59',0,0),(60,0,'Variable','Variable','Variable',1,60,'60',0,0),(61,0,'Parameter','Parameter','Parameter',1,61,'61',0,0),(62,0,'Observation','Observation','Observation',1,62,'62',0,0),(63,0,'ProcLevel','Processing Level','Processing Level',1,63,'63',0,0),(64,0,'DataChannel','Data Channel','Data Channel',1,64,'64',0,0),(65,0,'Version','Version','Version',1,65,'65',0,0),(66,0,'WFTemplate','Workflow Template','Workflow Template',1,66,'66',0,0),(67,0,'WFInstance','Workflow Instance','Workflow Instance',1,67,'67',0,0),(68,0,'Notif','Notification','Notification',1,68,'68',0,0),(69,0,'DataSrc','Data Source','Data Source',1,69,'69',0,0),(70,0,'DataStore','Data Store','Data Store',1,70,'70',0,0),(71,0,'Instrument','Instrument','Instrument',1,71,'71',0,0),(72,0,'GeoCoord','Geo Coordinates','Geo Coordinates',1,72,'72',0,0),(73,0,'TempCover','Temporal Coverage','Temporal Coverage',1,73,'73',0,0); UNLOCK TABLES; /*!40000 ALTER TABLE lead_attribute_definition ENABLE KEYS */; -- -- Table structure for table lead_element_definition -- DROP TABLE IF EXISTS lead_element_definition ; CREATE TABLE lead_element_definition ( Element_id int(11) NOT NULL auto_increment, Attribute_id int(11) NOT NULL default 0, Element_type int(11) NOT NULL default 0, Element_name varchar(100) NOT NULL default '', Element_description varchar(255) NOT NULL default '', Element_short_desc varchar(30) NOT NULL default '', PRIMARY KEY ( Element_id , Attribute_id ), KEY Element_name ( Element_name ) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; -- -- Loading element definitions for table: lead_element_definition -- /*!40000 ALTER TABLE lead_element_definition DISABLE KEYS */; LOCK TABLES lead_element_definition WRITE; INSERT INTO lead_element_definition ( Element_id , Attribute_id , Element_type , Element_name , Element_description , Element_short_desc ) VALUES (1,1,6,'Platform','System used to generate or collect the data - this could be a sensor or a platform','platform'),(2,2,4,'DbtTemp','Dry bulb temperature value','temperature'),(3,2,6,'DbtUom','Dry bulb temperature units of measure','units of measure'),(4,3,4,'DewPoint','Dew point','dew point'),(5,4,4,'WbtTemp','Wet bulb temperature value','temperature'),(6,4,6,'WbtUom','Wet bulb temperature units of measure','units of measure'),(7,5,4,'VertWindSpeed','Vertical wind speed','wind speed'),(8,5,6,'VertWindUom','Vertical wind speed units of measure','speed units of measure'),(9,5,6,'VertWindDir','Vertical wind direction - updraft or downdraft','wind direction'),(10,6,4,'ZonWindSpeed','Zonal wind speed - positive or negative indicates direction','wind speed'),(11,6,6,'ZonWindUom','Zonal wind speed units of measure','units of measure'),(12,7,4,'MerWindSpeed','Meridional wind speed - positive or negative indicates direction','wind speed'),(13,7,6,'MerWindUom','Meridional wind speed units of measure','units of measure'),(14,8,4,'WindSpeed','wind speed','wind speed'),(15,8,6,'WindSpeedUom','wind speed units of measure','wind speed units of measure'),(16,9,6,'windDir','wind direction','wind direction'),(17,11,4,'RelHunid','Relative humidity','Relative humidity'),(18,13,4,'MSLP','Mean Sea Level Pressure','Mean Sea Level Pressure'),(19,14,4,'Altimeter','Altimeter Setting','Altimeter Setting'),(20,18,4,'AbsHunid','Absolute Humidity','Absolute Humidity'),(21,19,4,'SpecHunid','Specific Humidity','Specific Humidity'),(22,20,4,'MixRatioVal','Mixing Ratio','Mixing Ratio'),(23,20,6,'MixRatioConst','Mixing Ratio Constituent','Mixing Ratio Constituent'),(24,26,4,'SpectrumWidth','Spectrum Width','Spectrum Width'),(25,27,4,'AccumPrecip','Accumulated Precipitation','Accumulated Precipitation'),(26,28,4,'CTT','Cloud Top Temperature','Cloud Top Temperature'),(27,29,4,'SurfAlbedo','Surface Albedo','Surface Albedo'),(28,30,4,'SST','Surface Skin Temperature','Surface Skin Temperature'),(29,31,4,'WST','Water Surface Temperature','Water Surface Temperature'),(30,32,4,'PrecipWater','Precipitable Water','Precipitable Water'),(31,33,5,'Dimensionality','Dimensionality','Dimensionality'),(32,34,7,'GeoHorzCov','Geographic coverage horizontal coverage (point or polygon)','Geographic horizontal coverage'),(33,34,6,'GeoVertType','Vertical spatial type','Vertical spatial type'),(34,34,4,'GeoVertValue','Vertical spatial value','Vertical spatial value'),(35,36,6,'VertCRSDatum','Vertical coordinate reference system datum name','Vertical CRS datum name'),(36,36,6,'VertCRSUnit','Vertical coordinate reference system datum unit','Vertical CRS datum unit'),(37,36,4,'VertCRSRes','Vertical coordinate reference system vertical resolution','Vertical CRS resolution'),(38,37,6,'HorzGCRSUnit','Horizontal geographic coordinate reference system unit','Horizontal geographic CRS unit'),(39,37,4,'HorzGCRSLat','Horizontal geographic coordinate reference system latitude resolution','Horizontal geographic CRS lati'),(40,37,4,'HorzGCRSLon','Horizontal geographic coordinate reference system longitude resolution','Horizontal geographic CRS long'),(41,46,6,'HorzLCRSDesc','Horizontal local coordinate reference system description','Horizontal local CRS descripti'),(42,46,6,'HorzLCRSInfo','Horizontal local coordinate reference system geographic reference information (registers local coordinate system to Earth)','Horizontal local CRS reference'),(43,47,6,'HorzLCRSModel','Horizontal local coordinate reference system geodetic model','Horizontal local CRS geodetic'),(44,39,6,'PlanarInfoMethod','Horizontal planar coordinate reference system information coordinate encoding method','Planar CRS info encoding metho'),(45,39,6,'PlanarInfoUnits','Horizontal planar coordinate reference system information distance units','Planar CRS info distance units'),(46,40,4,'PlanarInfoDBRDistRes','Horizontal planar coordinate information - distance bearing representation - distance resolution','Planar DBR distance resolution'),(47,40,6,'PlanarInfoDBRUnits','Horizontal planar coordinate information - distance bearing representation - units','Planar DBR distance units'),(48,40,6,'PlanarInfoDBRDir','Horizontal planar coordinate information - distance bearing representation - direction','Planar DBR distance direction'),(49,40,4,'PlanarInfoDBRRes','Horizontal planar coordinate information - distance bearing representation - resolution','Planar DBR resolution'),(50,40,6,'PlanarInfoDBRMer','Horizontal planar coordinate information - distance bearing representation - meridian','Planar DBR distance meridian'),(51,41,4,'PlanarInfoAbcissa','Horizontal planar coordinate information - coordinate representation - abcissa resolution','Planar coordinate representati'),(52,41,4,'PlanarInfoOrdinate','Horizontal planar coordinate information - coordinate representation - ordinate resolution','Planar coordinate representati'),(53,43,6,'PlanarCSLocalRef','Horizontal planar coordinate system - local geographic reference information','Planar coordinate system local'),(54,43,6,'PlanarCSLocalDesc','Horizontal planar coordinate system - local system description','Planar coordinate system local'),(55,44,6,'PlanarCSGridName','Horizontal planar coordinate system - grid system name','Planar coordinate system grid'),(56,45,6,'PlanarCSMapName','Horizontal planar coordinate system - map projection name','Planar coordinate system map n'),(57,45,6,'PlanarCSMapParam','Horizontal planar coordinate system - map projection parameter','Planar coordinate system map p'),(58,48,3,'TemporalCovStart','Temporal coverage start date-time','Temporal coverage start'),(59,48,3,'TemporalCovEnd','Temporal coverage end date-time','Temporal coverage end'),(60,49,6,'Avail','Availability of the data','Availability'),(61,50,6,'Format','Data Format','Data Format'),(62,51,6,'AccessCon','Access Constraints','Access Constraints'),(63,52,6,'UseCon','Usage Constraints','Usage Constraints'),(64,54,6,'QA','Quality Assurance','Quality Assurance'),(65,55,6,'ContactName','Provider contact name','Provider name'),(66,55,6,'ContactOrg','Provider contact organization','Provider organization'),(67,55,6,'ContactPhone','Provider contact telephone','Provider telephone'),(68,55,6,'ContactEmail','Provider contact email address','Provider email'),(69,56,6,'ContactAddrSt','Provider contact street address','Provider street address'),(70,56,6,'ContactAddrCity','Provider contact address city','Provider city'),(71,56,6,'ContactAddrState','Provider contact address state','Provider state'),(72,56,6,'ContactAddrZip','Provider contact address postal code','Provider zip code'),(73,56,6,'ContactAddrCountry','Provider contact address country','Provider country'),(74,57,6,'Discipline','Discipline','Discipline'),(75,58,6,'Topic','Topic','Topic'),(76,59,6,'Term','Term','Term'),(77,60,6,'Variable','Variable','Variable'),(78,61,6,'Parameter','Parameter','Parameter'),(79,62,6,'ObsCampName','Observation campaign name','Observation name'),(80,62,1,'ObsCampStart','Observation campaign start date','Observation start date'),(81,62,1,'ObsCampEnd','Observation campaign end date','Observation end date'),(82,62,6,'ObsCampObj','Observation campaign objective','Observation objective'),(83,63,6,'ProcLevel','Processing level','Processing level'),(84,64,6,'DataChannel','Data channel','Data channel'),(85,65,5,'Version','Version','Version'),(86,66,6,'WFTempName','Name of the workflow template','Workflow template name'),(87,66,6,'WFTempDesc','Descriptionof the workflow template','Workflow template description'),(88,66,8,'WFTempCont','Workflow template contents','Workflow template contents'),(89,67,6,'WFInstName','Name of the workflow instance','Workflow instance name'),(90,67,6,'WFInstDesc','Description of the workflow instance','Workflow instance description'),(91,67,8,'WFInstCont','Workflow instance contents','Workflow instance contents'),(92,68,3,'NotifTime','Notification timestamp','Notification timestamp'),(93,68,6,'NotifStat','Notification status','Notification status'),(94,68,6,'NotifMsg','Notification message','Notification message'),(95,68,6,'NotifSrc','Notification source','Notification source'),(96,69,6,'DSrcAccProt','Data Source Access Protocol','Data Src Acc Protocol'),(97,69,6,'DSrcGeoQuan','Data Source Geo Quantity','Data Src Geo Quantity'),(98,69,6,'DSrcCreator','Data Source Creator','Data Src Creator'),(99,69,6,'DSrcFormat','Data Source Data Format','Data Source Data Format'),(100,69,6,'DSrcDesc','Data Source Description','Data Source Description'),(101,69,6,'DSrcPub','Data Source Publisher','Data Src Publisher'),(102,69,4,'DSrcEWSize','Data Source Spatial Coverage EW Size','Data Src EW Size'),(103,69,4,'DSrcEWStart','Data Source Spatial Coverage EW Start','Data Src EW Start'),(104,69,4,'DSrcNSSize','Data Source Spatial Coverage NS Size','Data Src NS Size'),(105,69,4,'DSrcNSStart','Data Source Spatial Coverage NS Start','Data Src NS Start'),(106,69,4,'DSrcUDSize','Data Source Spatial Coverage UD Size','Data Src UD Size'),(107,69,4,'DSrcUDStart','Data Source Spatial Coverage UD Start','Data Src UD Start'),(108,69,6,'DSrcCovName','Data Source Coverage Name','Data Src Coverage Name'),(109,69,6,'DSrcZPos','Data Source Spatial Coverage Z Positive','Data Src Z Positive'),(110,69,6,'DSrcSubj','Data Source Subject','Data Src Subject'),(111,69,3,'DSrcCovBgn','Data Source Temporal Coverage Begin','Data Source Temp Cov Begin'),(112,69,3,'DSrcCovEnd','Data Source Temporal Coverage End','Data Source Temp Cov End'),(113,69,6,'DSrcTitle','Data Source Title','Data Src Title'),(114,69,6,'DSrcRef','Data Source Reference','Data Source Reference'),(115,69,6,'DSrcTyp','Data Source Type','Data Source Type'),(116,69,6,'DSrcColl','Data Source Collection','Data Source Collection'),(117,70,6,'DStoRef','Data Store Reference','Data Store Reference'),(118,70,6,'DStoTyp','Data Store Type','Data Store Type'),(119,70,6,'DStoName','Data Store Name','Data Store Name'),(120,70,6,'DStoAccSvc','Data Store Access Service','Data Store Acces Service'),(121,71,6,'Instrument','Instrument','Instrument'),(122,72,4,'GeoLat','Latitude','Latitude'),(123,72,4,'GeoLon','Longitude','Longitude'),(124,73,6,'TempCovBegin','Temporal Coverage Begin','Temporal Coverage Begin'),(125,73,6,'TempCovEnd','Temporal Coverage End','Temporal Coverage End'); UNLOCK TABLES; /*!40000 ALTER TABLE lead_element_definition ENABLE KEYS */; -- -- Table structure for table: lead_type_definition -- -- In myLEAD we have two actual container types: -- Files and Collections. Files must always -- be contained within some type of collection -- but additional levels of the hierarchy can -- be defined for the collections. -- Files are Lead_type = 4, and collections -- are 5 and higher. Each container can only -- contain collections or files with the same -- or a lower type. The exception is that -- files cannot contain any other container -- (they are the leaves in the hierarchy). -- DROP TABLE IF EXISTS lead_type_definition ; CREATE TABLE lead_type_definition ( Type_name varchar(50) NOT NULL default '', Lead_type tinyint(4) NOT NULL default 0, Type_tag varchar(30) NOT NULL default '', Type_description varchar(255) NOT NULL default '', PRIMARY KEY ( Type_name ), UNIQUE KEY Level ( Lead_type ) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; -- -- Adding definitions to table: lead_type_definition -- /*!40000 ALTER TABLE lead_type_definition DISABLE KEYS */; LOCK TABLES lead_type_definition WRITE; INSERT INTO lead_type_definition ( Type_name , Lead_type , Type_tag , Type_description ) VALUES ('FILE',4,'file','File'),('COLLECTION',5,'collection','Collection'),('EXPERIMENT',6,'experiment','Experiment'),('PROJECT',7,'project','Project'); UNLOCK TABLES; /*!40000 ALTER TABLE lead_type_definition ENABLE KEYS */; -- -- Table structure for table: lead_param_definition -- -- This table provides a cross-reference from -- parameter tag names in processing a query -- to integer IDs used in processing the query. DROP TABLE IF EXISTS lead_param_definition ; CREATE TABLE lead_param_definition ( Tag_name varchar(30) NOT NULL default '', Param_type tinyint(4) NOT NULL default 0, PRIMARY KEY ( Tag_name ) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; -- -- Adding definitions to table: lead_param_definition -- /*!40000 ALTER TABLE lead_param_definition DISABLE KEYS */; LOCK TABLES lead_param_definition WRITE; INSERT INTO lead_param_definition ( Tag_name , Param_type ) VALUES ('name',1),('description',2),('createDateStart',3),('createDateEnd',4),('type',5),('collectionDateStart',6),('collectionDateEnd',7); UNLOCK TABLES; /*!40000 ALTER TABLE lead_param_definition ENABLE KEYS */; -- ******************************************* -- * Parent Relations * -- ******************************************* -- -- Table structure for table: lead_parent -- -- This table holds the relationship between -- parent and child collections and files. -- This holds all parent-child relationships -- both direct and indirect. The top level -- in the hierarchy (has no parent) must be a -- collection of some type and has a level = 1. -- DROP TABLE IF EXISTS lead_parent ; CREATE TABLE lead_parent ( Parent_id int(11) NOT NULL default 0, Parent_type tinyint(4) NOT NULL default 0, Parent_level tinyint(4) NOT NULL default 1, Child_id int(11) NOT NULL default 0, Child_type tinyint(4) NOT NULL default 0, Child_level tinyint(4) NOT NULL default 0, PRIMARY KEY ( Parent_id , Parent_type , Child_id , Child_type ) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; -- ******************************************* -- * Collections * -- ******************************************* -- -- Table structure for table mcs_collection -- DROP TABLE IF EXISTS mcs_collection ; CREATE TABLE mcs_collection ( Collection_id INTEGER NOT NULL auto_increment, Global_id VARCHAR(50) NOT NULL, Lead_type TINYINT NOT NULL default 0, Collection_name VARCHAR(255) NOT NULL default '', Collection_desc VARCHAR(255) default NULL, Logical_date datetime NOT NULL default '0000-00-00 00:00:00', Creator_id int(11) NOT NULL default 0, Last_modifier_id int(11) default NULL, Create_time datetime default NULL, Last_modify_time datetime default NULL, audit int(11) default NULL, xml text, PRIMARY KEY ( Collection_id ), UNIQUE KEY Collection_name ( Collection_name, Creator_id, Lead_type ), KEY Creator_id ( Creator_id ), KEY Ctype ( Collection_id , Lead_type ), KEY index3 ( Collection_id , Collection_name ) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; -- -- Table structure for table collection_attributes -- DROP TABLE IF EXISTS collection_attributes; CREATE TABLE collection_attributes ( Object_id int(11) NOT NULL default 0, Attribute_id int(11) NOT NULL default 0, Sequence_id int(11) NOT NULL auto_increment, PRIMARY KEY (Object_id,Attribute_id,Sequence_id) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; -- -- Table structure for table mcs_coll_date_elements -- DROP TABLE IF EXISTS mcs_coll_date_elements ; CREATE TABLE mcs_coll_date_elements ( Object_id int(11) NOT NULL default 0, Attribute_id int(11) NOT NULL default 0, Sequence_id int(11) NOT NULL default 0, Element_id int(11) NOT NULL default 0, Element_value date NOT NULL default '0000-00-00', PRIMARY KEY ( Object_id , Attribute_id , Sequence_id , Element_id ) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; -- -- Table structure for table mcs_coll_time_elements -- DROP TABLE IF EXISTS mcs_coll_time_elements ; CREATE TABLE mcs_coll_time_elements ( Object_id int(11) NOT NULL default 0, Attribute_id int(11) NOT NULL default 0, Sequence_id int(11) NOT NULL default 0, Element_id int(11) NOT NULL default 0, Element_value time NOT NULL default '00:00:00', PRIMARY KEY ( Object_id , Attribute_id , Sequence_id , Element_id ) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; -- -- Table structure for table mcs_coll_datetime_elements -- DROP TABLE IF EXISTS mcs_coll_datetime_elements ; CREATE TABLE mcs_coll_datetime_elements ( Object_id int(11) NOT NULL default 0, Attribute_id int(11) NOT NULL default 0, Sequence_id int(11) NOT NULL default 0, Element_id int(11) NOT NULL default 0, Element_value datetime NOT NULL default '0000-00-00 00:00:00', PRIMARY KEY ( Object_id , Attribute_id , Sequence_id , Element_id ) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; -- -- Table structure for table mcs_coll_float_elements -- DROP TABLE IF EXISTS mcs_coll_float_elements ; CREATE TABLE mcs_coll_float_elements ( Object_id int(11) NOT NULL default 0, Attribute_id int(11) NOT NULL default 0, Sequence_id int(11) NOT NULL default 0, Element_id int(11) NOT NULL default 0, Element_value float NOT NULL default 0, PRIMARY KEY ( Object_id , Attribute_id , Sequence_id , Element_id ) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; -- -- Table structure for table mcs_coll_int_elements -- DROP TABLE IF EXISTS mcs_coll_int_elements ; CREATE TABLE mcs_coll_int_elements ( Object_id int(11) NOT NULL default 0, Attribute_id int(11) NOT NULL default 0, Sequence_id int(11) NOT NULL default 0, Element_id int(11) NOT NULL default 0, Element_value int(11) NOT NULL default 0, PRIMARY KEY ( Object_id , Attribute_id , Sequence_id , Element_id ) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; -- -- Table structure for table mcs_coll_spatial_elements -- DROP TABLE IF EXISTS mcs_coll_spatial_elements ; CREATE TABLE mcs_coll_spatial_elements ( Object_id int(11) NOT NULL default 0, Attribute_id int(11) NOT NULL default 0, Sequence_id int(11) NOT NULL default 0, Element_id int(11) NOT NULL default 0, Element_value geometry NOT NULL default '', PRIMARY KEY ( Object_id , Attribute_id , Sequence_id , Element_id ), SPATIAL KEY Element_value ( Element_value (32)) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; -- -- Table structure for table mcs_coll_string_elements -- DROP TABLE IF EXISTS mcs_coll_string_elements ; CREATE TABLE mcs_coll_string_elements ( Object_id int(11) NOT NULL default 0, Attribute_id int(11) NOT NULL default 0, Sequence_id int(11) NOT NULL default 0, Element_id int(11) NOT NULL default 0, Element_value varchar(250) NOT NULL default '', PRIMARY KEY ( Object_id , Attribute_id , Sequence_id , Element_id ) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; -- -- Table structure for table mcs_coll_text_elements -- DROP TABLE IF EXISTS mcs_coll_text_elements ; CREATE TABLE mcs_coll_text_elements ( Object_id int(11) NOT NULL, Attribute_id int(11) NOT NULL, Sequence_id int(11) NOT NULL, Element_id int(11) NOT NULL, Element_value mediumtext NOT NULL, PRIMARY KEY ( Object_id , Attribute_id , Sequence_id , Element_id ) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; -- ******************************************* -- * Files * -- ******************************************* -- -- Table structure for table mcs_logical_file -- DROP TABLE IF EXISTS mcs_logical_file ; CREATE TABLE mcs_logical_file ( Data_id int(11) NOT NULL auto_increment, Global_id varchar(50) NOT NULL, Logical_name varchar(255) NOT NULL default '', Logical_desc varchar(255) default NULL, Data_type varchar(250) default NULL, Collection_id int(11) NOT NULL default 0, Container_service varchar(250) default NULL, Is_valid int(11) default NULL, Creator_id int(11) NOT NULL default 0, Last_modifier_id int(11) default NULL, Create_time datetime default NULL, Last_modify_time datetime default NULL, Master_copy varchar(250) default NULL, audit int(11) default 0, xml text, PRIMARY KEY (Data_id), UNIQUE KEY Logical_name (Logical_name, Creator_id), KEY Creator_id (Creator_id), KEY index4 (Collection_id), KEY index3 (Data_id , Logical_name) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; -- -- Table structure for table file_attributes -- DROP TABLE IF EXISTS file_attributes ; CREATE TABLE file_attributes ( Object_id int(11) NOT NULL default 0, Attribute_id int(11) NOT NULL default 0, Sequence_id int(11) NOT NULL auto_increment, PRIMARY KEY ( Object_id , Attribute_id , Sequence_id ) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; -- -- Table structure for table mcs_file_date_elements -- DROP TABLE IF EXISTS mcs_file_date_elements ; CREATE TABLE mcs_file_date_elements ( Object_id int(11) NOT NULL default 0, Attribute_id int(11) NOT NULL default 0, Sequence_id int(11) NOT NULL default 0, Element_id int(11) NOT NULL default 0, Element_value date NOT NULL default '0000-00-00', PRIMARY KEY ( Object_id , Attribute_id , Sequence_id , Element_id ) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; -- -- Table structure for table mcs_file_time_elements -- DROP TABLE IF EXISTS mcs_file_time_elements ; CREATE TABLE mcs_file_time_elements ( Object_id int(11) NOT NULL default 0, Attribute_id int(11) NOT NULL default 0, Sequence_id int(11) NOT NULL default 0, Element_id int(11) NOT NULL default 0, Element_value time NOT NULL default '00:00:00', PRIMARY KEY ( Object_id , Attribute_id , Sequence_id , Element_id ) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; -- -- Table structure for table mcs_file_datetime_elements -- DROP TABLE IF EXISTS mcs_file_datetime_elements ; CREATE TABLE mcs_file_datetime_elements ( Object_id int(11) NOT NULL default 0, Attribute_id int(11) NOT NULL default 0, Sequence_id int(11) NOT NULL default 0, Element_id int(11) NOT NULL default 0, Element_value datetime NOT NULL default '0000-00-00 00:00:00', PRIMARY KEY ( Object_id , Attribute_id , Sequence_id , Element_id ) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; -- -- Table structure for table mcs_file_float_elements -- DROP TABLE IF EXISTS mcs_file_float_elements ; CREATE TABLE mcs_file_float_elements ( Object_id int(11) NOT NULL default 0, Attribute_id int(11) NOT NULL default 0, Sequence_id int(11) NOT NULL default 0, Element_id int(11) NOT NULL default 0, Element_value float NOT NULL default 0, PRIMARY KEY ( Object_id , Attribute_id , Sequence_id , Element_id ) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; -- -- Table structure for table mcs_file_int_elements -- DROP TABLE IF EXISTS mcs_file_int_elements ; CREATE TABLE mcs_file_int_elements ( Object_id int(11) NOT NULL default 0, Attribute_id int(11) NOT NULL default 0, Sequence_id int(11) NOT NULL default 0, Element_id int(11) NOT NULL default 0, Element_value int(11) NOT NULL default 0, PRIMARY KEY ( Object_id , Attribute_id , Sequence_id , Element_id ) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; -- -- Table structure for table mcs_file_spatial_elements -- DROP TABLE IF EXISTS mcs_file_spatial_elements ; CREATE TABLE mcs_file_spatial_elements ( Object_id int(11) NOT NULL default 0, Attribute_id int(11) NOT NULL default 0, Sequence_id int(11) NOT NULL default 0, Element_id int(11) NOT NULL default 0, Element_value geometry NOT NULL default '', PRIMARY KEY ( Object_id , Attribute_id , Sequence_id , Element_id ), SPATIAL KEY Element_value ( Element_value (32)) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; -- -- Table structure for table mcs_file_string_elements -- DROP TABLE IF EXISTS mcs_file_string_elements ; CREATE TABLE mcs_file_string_elements ( Object_id int(11) NOT NULL default 0, Attribute_id int(11) NOT NULL default 0, Sequence_id int(11) NOT NULL default 0, Element_id int(11) NOT NULL default 0, Element_value varchar(250) NOT NULL default '', PRIMARY KEY ( Object_id , Attribute_id , Sequence_id , Element_id ) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; -- -- Table structure for table mcs_file_text_elements -- DROP TABLE IF EXISTS mcs_file_text_elements ; CREATE TABLE mcs_file_text_elements ( Object_id int(11) NOT NULL, Attribute_id int(11) NOT NULL, Sequence_id int(11) NOT NULL, Element_id int(11) NOT NULL, Element_value mediumtext NOT NULL, PRIMARY KEY ( Object_id , Attribute_id , Sequence_id , Element_id ) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; -- *************************************** -- Table structure for table mcs_writer -- *************************************** DROP TABLE IF EXISTS mcs_writer; CREATE TABLE mcs_writer ( Writer_id int(11) NOT NULL auto_increment, Writer_Dn varchar(250) NOT NULL default '', Writer_replica INTEGER NOT NULL DEFAULT 0, -- Replica_id from lead_replica_info table Writer_name VARCHAR(255) NOT NULL DEFAULT "", -- replaces first and last name to comply with FGDC Writer_organization VARCHAR(255) NOT NULL DEFAULT "", Writer_position VARCHAR(255) NOT NULL DEFAULT "", Writer_addr_type VARCHAR(255) default NULL, Writer_street VARCHAR(255) default NULL, Writer_city VARCHAR(255) default NULL, Writer_state VARCHAR(64) default NULL, Writer_postcode VARCHAR(64) default NULL, Writer_country VARCHAR(64) default NULL, Writer_phone varchar(250) default NULL, Writer_ttdtty_phone varchar(250) default NULL, Writer_email varchar(250) default NULL, Writer_fax VARCHAR(250) default NULL, Writer_url VARCHAR(255) default NULL, Writer_hours VARCHAR(255) default NULL, Writer_contact_instructions VARCHAR(255) default NULL, Creator_id int(11) NOT NULL, Last_Modifier_id int(11) default NULL, Create_Time datetime default NULL, Last_Modify_Time datetime default NULL, PRIMARY KEY (Writer_id), UNIQUE KEY Dname (Writer_Dn) ) ENGINE=MyISAM CHARSET=latin1; -- -- Table structure for table 'lead_replica_info' -- DROP TABLE IF EXISTS lead_replica_info; CREATE TABLE lead_replica_info ( Replica_id INTEGER NOT NULL auto_increment, Replica_nickname VARCHAR(255) NOT NULL DEFAULT '', Replica_service_url VARCHAR(255) NOT NULL DEFAULT '', Replica_location VARCHAR(255) NOT NULL DEFAULT '', Replica_type TINYINT NOT NULL DEFAULT 0, -- ........................... 1 = master Replica_port INTEGER NOT NULL DEFAULT 0, Replica_global_id VARCHAR(255) NOT NULL DEFAULT '', Replica_last_update DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00', -- last update to replica, not metadata Replica_update_desc VARCHAR(255) NOT NULL DEFAULT '', Replica_server_url VARCHAR(255) NOT NULL DEFAULT '', Replica_creator_id int(11) NOT NULL, -- ...................................... who added the replica info Replica_create_time datetime default NULL, -- ................................. when was the replica info created Replica_last_modify_time datetime default NULL, Replica_last_modifier_id INTEGER DEFAULT NULL, PRIMARY KEY (Replica_id), UNIQUE KEY nickname (Replica_nickname)); -- -- Table structure for table 'lead_storage_resources' -- DROP TABLE IF EXISTS lead_storage_resources; CREATE TABLE lead_storage_resources ( Writer_id INTEGER NOT NULL, -- ID from the mcs_writer table Resource_id INTEGER auto_increment, -- internal ID unique within a user Access_url VARCHAR(255) NOT NULL DEFAULT '', Nickname VARCHAR(255) NOT NULL, Resource_global_id VARCHAR(255) NOT NULL DEFAULT '', PRIMARY KEY (Writer_id, Resource_id), UNIQUE KEY nickname(Writer_id, Resource_id, Nickname)); -- -- Table structure for table 'lead_storage_protocols' -- DROP TABLE IF EXISTS lead_storage_protocols; CREATE TABLE lead_storage_protocols ( Writer_id INTEGER NOT NULL, -- ID from the mcs_writer table Resource_id INTEGER NOT NULL, -- ID from lead_storage_resources Protocol VARCHAR(255) NOT NULL, PRIMARY KEY (Writer_id, Resource_id, Protocol));