-- --------------------------------------------------------- -- myLEAD Version 1.2 -- Server version 5.0.16 -- Indiana University -- Distributed Data Everywhere -- --------------------------------------------------------- use mcs_lead; -- ******************************************* -- * Definitions * -- ******************************************* -- -- Table structure for table lead_attribute_definition -- DROP TABLE IF EXISTS lead_attribute_definition ; CREATE TABLE lead_attribute_definition ( Attr_def_id INT NOT NULL AUTO_INCREMENT, Creator_id INT NOT NULL DEFAULT 0, Attribute_name VARCHAR(100) NOT NULL, Attribute_source VARCHAR(100) NOT NULL DEFAULT 'LEAD', Schema_order_id SMALLINT NOT NULL DEFAULT 0, Attribute_description VARCHAR(255) NOT NULL DEFAULT '', Attribute_short_desc VARCHAR(30) NOT NULL DEFAULT '', Attribute_type INT NOT NULL DEFAULT 1, Top_attr_id INT NOT NULL DEFAULT 0, Parent_sequence VARCHAR(100) NOT NULL DEFAULT '', Parent_id INT NOT NULL DEFAULT 0, PRIMARY KEY ( Attr_def_id ), UNIQUE KEY Attribute_namesrc (Attribute_name, Attribute_source), UNIQUE KEY parent (Parent_id, Attribute_name, Attr_def_id), 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 (Attr_def_id, Attribute_name, Attribute_source, Schema_order_id, Attribute_description, Attribute_short_desc, Attribute_type, Top_attr_id, Parent_sequence, Parent_id) VALUES (1,'citation','LEAD',5,'citation','citation',1,1,'1',0), (2,'seriesInfo','LEAD',0,'series information','series info',1,1,'1.2',1), (3,'pubInfo','LEAD',0,'publication information','pub info',1,1,'1.3',1), (4,'description','LEAD',6,'description','description',1,4,'4',0), (5,'status','LEAD',7,'status','status',1,5,'5',0), (6,'accessConstraint','LEAD',8,'access constraints','access constraints',1,6,'6',0), (7,'useConstraint','LEAD',9,'usage constraints','usage constraints',1,7,'7',0), (8,'keywordTheme','LEAD',11,'theme keywords','theme keywords',1,8,'8',0), (9,'keywordPlace','LEAD',12,'geographic place keywords','place keywords',1,9,'9',0), (10,'keywordStratum','LEAD',13,'layered vertical stratum keywords','stratum keywords',1,10,'10',0), (11,'keywordTemporal','LEAD',14,'temporal keywords','temporal keywords',1,14,'14',0), (12,'metadataInfo','LEAD',15,'metadata information','metadata info',1,15,'15',0), (13,'timePeriod','LEAD',18,'time period','time period',1,13,'13',0), (14,'timeInfoDate','LEAD',0,'single or multiple calendar date and time','single or multi date',1,13,'13.14',13), (15,'dateRange','LEAD',0,'range of dates and times','date range',1,13,'13.15',13), (16,'indeterminateDate','LEAD',0,'indeterminate date and time ranges','indeterminate date',1,13,'13.16',13), (17,'spatialBounds','LEAD',20,'spatial bounding coordinates','spatial bounds',1,17,'17',0), (18,'spatialVertical','LEAD',22,'vertical domain','vertical domain',1,18,'18',0), (19,'eaOverview','LEAD',25,'overview summary of dataset','overview',1,19,'19',0), (20,'distContact','LEAD',28,'distribution contact information','distribution contact',1,20,'20',0), (21,'contactAddr','LEAD',0,'contact address','contact address',1,20,'.20.21',20), (22,'stdOrderProcess','LEAD',29,'standard order process','order process',1,22,'22',0), (23,'dataQualComplete','LEAD',31,'data quality completeness report','data completeness',1,23,'23',0), (24,'dqProcessStep','LEAD',33,'data quality – lineage processing step','processing step',1,24,'24',0), (25,'dqSourceUsed','LEAD',0,'process step source used','source used',1,24,'24.25',24), (26,'dqSourceProduct','LEAD',0,'process step source product','source product',1,26,'24.26',24), (27,'childResource','LEAD',35,'enclosed child resource','enclosed resource',1,27,'27',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 NOT NULL AUTO_INCREMENT, Attr_def_id INT NOT NULL DEFAULT 0, Element_type TINYINT NOT NULL DEFAULT 0, Element_name VARCHAR(100) NOT NULL DEFAULT '', Element_source VARCHAR(100) NOT NULL DEFAULT 'LEAD', Element_description VARCHAR(255) NOT NULL DEFAULT '', Element_short_desc VARCHAR(30) NOT NULL DEFAULT '', PRIMARY KEY ( Element_id, Attr_def_id ), UNIQUE KEY Element_def (Attr_def_id, Element_name, Element_source) ) 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, Attr_def_id, Element_type, Element_name, Element_source, Element_description, Element_short_desc ) VALUES (1,1,6,'originator','LEAD','citation originator','originator'), (2,1,1,'pubDate','LEAD','publication date','publication date'), (3,1,2,'pubTime','LEAD','publication time','publication time'), (4,1,6,'title','LEAD','title','title'), (5,1,6,'edition','LEAD','edition – version of the title','edition'), (6,1,6,'geoFormat','LEAD','geospatial data presentation format','geospatial format'), (7,1,6,'otherCite','LEAD','other citation details','other cite details'), (8,1,6,'onlink','LEAD','online linkage','online linkage'), (9,2,6,'seriesName','LEAD','series publication name','series name'), (10,2,6,'seriesIssue','LEAD','series publication issue','series issue'), (11,3,6,'pubPlace','LEAD','publication city','publication place'), (12,3,6,'publisher','LEAD','publisher name','publisher'), (13,4,6,'abstract','LEAD','abstract','abstract'), (14,4,6,'purpose','LEAD','purpose','purpose'), (15,4,6,'supplementalInfo','LEAD','supplemental information','supplemental info'), (16,5,6,'progress','LEAD','progress','progress'), (17,5,6,'update','LEAD','update frequency','update freq'), (18,6,6,'accessConstraint','LEAD','access constraints','access constraints'), (19,7,6,'useConstraint','LEAD','usage constraints','usage constraints'), (20,8,6,'themeThesaurus','LEAD','theme keyword thesaurus','theme thesaurus'), (21,8,6,'themeKeyword','LEAD','theme keyword','theme keyword'), (22,9,6,'placeThesaurus','LEAD','place keyword thesaurus','place thesaurus'), (23,9,6,'placeKeyword','LEAD','place keyword','place keyword'), (24,10,6,'stratumThesaurus','LEAD','stratum keyword thesaurus','stratum thesaurus'), (25,10,6,'stratumKeyword','LEAD','stratum keyword','stratum keyword'), (26,11,6,'temporalThesaurus','LEAD','temporal keyword thesaurus','temporal thesaurus'), (27,11,6,'temporalKeyword','LEAD','temporal keyword','temporal keyword'), (28,12,1,'metadataDate','LEAD','date the metadata was created','metadata date'), (29,12,6,'metadataStdName','LEAD','metadata standard used','metadata standard'), (30,12,6,'metadataStdVersion','LEAD','version of metadata standard used','metadata standard version'), (31,13,6,'timePeriodCurrentness','LEAD','time period currentness reference','currentness'), (32,14,1,'calendarDate','LEAD','calendar date','calendar date'), (33,14,2,'calendarTime','LEAD','calendar time of day','calendar time'), (34,15,1,'beginDate','LEAD','beginning date','beginning date'), (35,15,2,'beginTime','LEAD','beginning time','beginning time'), (36,15,1,'endDate','LEAD','ending date','ending date'), (37,15,2,'endTime','LEAD','ending time','ending time'), (38,16,6,'dateBegin','LEAD','beginning date','beginning date'), (39,16,6,'dateEnd','LEAD','ending date','ending date'), (40,16,6,'duration','LEAD','duration','duration'), (41,17,5,'westCoordinate','LEAD','west bounding coordinate','west coordinate'), (42,17,5,'eastCoordinate','LEAD','east bounding coordinate','east coordinate'), (43,17,5,'northCoordinate','LEAD','north bounding coordinate','north coordinate'), (44,17,5,'southCoordinate','LEAD','south bounding coordinate','south coordinate'), (45,17,7,'boundingBox','LEAD','bounding box based on coordinates','bounding box'), (46,18,5,'lowerBound','LEAD','lower vertical spatial bound','lower bound'), (47,18,5,'upperBound','LEAD','upper vertical spatial bound','upper bound'), (48,19,6,'eaOverview','LEAD','entity and attribute overview','overview'), (49,19,6,'eaDetailCite','LEAD','entity and attribute detail citation','detail citation'), (50,20,6,'contactPerson','LEAD','contact person','contact person'), (51,20,6,'contactOrg','LEAD','contact organization','contact organization'), (52,20,6,'contactPos','LEAD','contact position','contact position'), (53,20,6,'contactPhone','LEAD','contact voice telephone','contact phone'), (54,20,6,'contactTTD','LEAD','contact hearing-impaired phone','contact TTDTTY phone'), (55,20,6,'contactFax','LEAD','contact facsimile phone','contact fax'), (56,20,6,'contactEMail','LEAD','contact email','contact email'), (57,20,6,'contactHours','LEAD','hours of service','hours'), (58,20,6,'contactInstruction','LEAD','contact instructions','contact instructions'), (59,21,6,'cntAddrType','LEAD','address type','address type'), (60,21,6,'cntAddrLine','LEAD','line of the address','address line'), (61,21,6,'cntAddrCity','LEAD','address city','city'), (62,21,6,'cntAddrState','LEAD','address state','state'), (63,21,6,'cntAddrZip','LEAD','address postal code','postal code'), (64,21,6,'cntAddrCountry','LEAD','address country','country'), (65,22,6,'formatName','LEAD','data transfer format name','format name'), (66,22,6,'formatVersionNum','LEAD','format version number','format version'), (67,22,1,'formatVersionDate','LEAD','format version date','format date'), (68,22,6,'formatSpec','LEAD','format specification','format specification'), (69,22,6,'formatInfoContent','LEAD','format information content','format content'), (70,22,6,'fileDecompression','LEAD','file decompression technique','file decompression'), (71,22,5,'transferSize','LEAD','transfer size','transfer size'), (72,23,6,'dataQualComplete','LEAD','data quality completeness report','data completeness'), (73,24,6,'dqProcessDesc','LEAD','process description','process description'), (74,24,6,'dqResourceID','LEAD','process resource ID','process resource ID'), (75,24,3,'dqProcessDate','LEAD','process date','process date'), (76,25,6,'dqResourceID','LEAD','source used resource ID','source used resource ID'), (77,25,6,'dqSourceType','LEAD','source used – type','source used – type'), (78,26,6,'dqResourceID','LEAD','product resource ID','source product resource ID'), (79,26,6,'dqSourceType','LEAD','product used – type','source product – type'), (80,27,6,'childResource','LEAD','childResource','childResource'); 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 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 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 */; -- ******************************************* -- * LEAD Schema Tables * -- ******************************************* -- -- Table structure for table lead_namespaces -- DROP TABLE IF EXISTS lead_namespaces; CREATE TABLE lead_namespaces ( Namespace_id TINYINT NOT NULL, Namespace_prefix VARCHAR(10) NOT NULL, Namespace_uri VARCHAR(200) NOT NULL, PRIMARY KEY ( Namespace_id), KEY Namespace_prefix (Namespace_id, Namespace_prefix) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; -- -- populate the table mlead_namespaces -- INSERT INTO lead_namespaces (Namespace_id, Namespace_prefix, Namespace_uri) VALUES (0, 'mylead', 'MYLEAD'), (1, 'lead', 'LEAD'), (2, 'le', 'LEADElements'), (3, 'fgdc', 'FGDC'); -- -- Table structure for table mylead_schema_map -- -- A cardinality setting of zero means unbounded -- DROP TABLE IF EXISTS mylead_schema_map; CREATE TABLE mylead_schema_map ( Namespace_id TINYINT NOT NULL, Schema_element_tag VARCHAR(30) NOT NULL, Schema_order_id SMALLINT NOT NULL, Schema_level TINYINT NOT NULL, Last_child_id SMALLINT NOT NULL, Cardinality SMALLINT NOT NULL DEFAULT 0, PRIMARY KEY ( Schema_order_id ) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; -- -- populate the table mylead_schema_map -- INSERT INTO mylead_schema_map ( Namespace_id, Schema_element_tag, Schema_order_id, Schema_level, Last_child_id, Cardinality) VALUES (1, 'LEADresource',1, 1,35,1), (2, 'resourceID', 2, 2, 2,1), (1, 'data', 3, 2, 35,1), (1, 'idinfo', 4, 3, 14,1), (1, 'citation', 5, 4, 5,1), (1, 'descript',6, 4, 6,1), (1, 'status', 7, 4, 7,1), (1, 'accconst', 8, 4, 8,1), (1, 'useconst', 9, 4, 9,1), (1, 'keywords',10, 4, 14,1), (3, 'theme', 11, 5, 11,0), (3, 'place', 12, 5, 12,0), (3, 'stratum', 13, 5, 13,0), (3, 'temporal', 14, 5, 14,0), (1, 'metainfo', 15, 3, 15,1), (1, 'geospatial', 16, 3, 25,1), (1, 'idinfo', 17, 4, 22,1), (1, 'timeperd', 18, 5, 18,1), (1, 'spdom', 19, 5, 21,1), (3, 'bounding', 20, 6, 20,1), (3, 'dspoly', 21, 6, 21,0), (2, 'vertdom', 22, 5, 22,1), (3, 'eainfo', 23, 4, 25,1), (3, 'detailed', 24, 5, 24,0), (3, 'overview', 25, 5, 25,0), (1, 'distinfo', 26, 3, 29,1), (3, 'distrib', 27, 4, 28,1), (3, 'cntinfo', 28, 5, 28,1), (3, 'stdorder', 29, 4, 29,0), (1, 'dataqual', 30, 3, 33,1), (3, 'complete', 31, 4, 31,1), (3, 'lineage', 32, 4, 33,1), (2, 'procstep', 33, 5, 33,0), (1, 'enclosedresources', 34, 3, 35,1), (2, 'resourceID', 35, 4, 35,0); -- -- Table structure for table mylead_schema_ancestors -- DROP TABLE IF EXISTS mylead_schema_ancestors; CREATE TABLE mylead_schema_ancestors ( Schema_order_id SMALLINT NOT NULL, Ancestor_id SMALLINT NOT NULL, PRIMARY KEY ( Schema_order_id, Ancestor_id) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; -- -- populate the table mylead_schema_ancestors -- INSERT INTO mylead_schema_ancestors (Schema_order_id, Ancestor_id) VALUES (2, 1), (5, 1), (5, 3), (5, 4), (6, 1), (6, 3), (6, 4), (7, 1), (7, 3), (7, 4), (8, 1), (8, 3), (8, 4), (9, 1), (9, 3), (9, 4), (11, 1), (11, 3), (11, 4), (11, 10), (12, 1), (12, 3), (12, 4), (12, 10), (13, 1), (13, 3), (13, 4), (13, 10), (14, 1), (14, 3), (14, 4), (14, 10), (15, 1), (15, 3), (18, 1), (18, 3), (18, 16), (18, 17), (20, 1), (20, 3), (20, 16), (20, 17), (20, 19), (21, 1), (21, 3), (21, 16), (21, 17), (21, 19), (22, 1), (22, 3), (22, 16), (22, 17), (24, 1), (24, 3), (24, 16), (24, 23), (25, 1), (25, 3), (25, 16), (25, 23), (28, 1), (28, 3), (28, 26), (28, 27), (29, 1), (29, 3), (29, 26), (31, 1), (31, 3), (31, 30), (33, 1), (33, 3), (33, 30), (33, 32), (35, 1), (35, 3), (35, 34); -- -- Table structure for table mylead_required_sibling -- -- This table tracks internal IDs of siblings -- required when an attribute (or higher level) -- is being added. The "sibling" must be an -- attribute node. DROP TABLE IF EXISTS mylead_required_sibling; CREATE TABLE mylead_required_sibling ( Schema_order_id SMALLINT NOT NULL, Sibling_id SMALLINT NOT NULL, PRIMARY KEY ( Schema_order_id, Sibling_id) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; -- -- populate the table mylead_required_sibling -- INSERT INTO mylead_required_sibling (Schema_order_id, Sibling_id) VALUES (21,20), (22, 20), (23, 18), (24, 18), (25, 18), (29, 28), (33, 31); -- -- Table structure for table lead_attribute_xml -- DROP TABLE IF EXISTS lead_attribute_xml; CREATE TABLE lead_attribute_xml ( Object_id BIGINT NOT NULL, Lead_type TINYINT NOT NULL, Clob_id INT NOT NULL, Schema_order_id SMALLINT NOT NULL, Attr_xml MEDIUMTEXT NOT NULL, PRIMARY KEY (Object_id, Lead_type, Clob_id), KEY Schema_order (Object_id, Lead_type, Schema_order_id) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- ******************************************* -- * 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. -- -- The Child_level index is needed in spQueryResults -- to get the maximum hierarchy level. -- DROP TABLE IF EXISTS lead_parent ; CREATE TABLE lead_parent ( Parent_id BIGINT NOT NULL DEFAULT 0, Parent_type TINYINT NOT NULL DEFAULT 0, Parent_level TINYINT NOT NULL DEFAULT 1, Child_id BIGINT NOT NULL DEFAULT 0, Child_type TINYINT NOT NULL DEFAULT 0, Child_level TINYINT NOT NULL DEFAULT 0, PRIMARY KEY ( Parent_id , Parent_type , Child_id , Child_type ), KEY Child_level (Child_level) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- ******************************************* -- * Collections * -- ******************************************* -- -- Table structure for table mcs_collection -- DROP TABLE IF EXISTS mcs_collection ; CREATE TABLE mcs_collection ( Collection_id BIGINT NOT NULL AUTO_INCREMENT, Parent_id BIGINT NOT NULL DEFAULT 0, Global_id VARCHAR(50) NOT NULL, Lead_type TINYINT NOT NULL, Creator_id INTEGER NOT NULL, Last_modifier_id INTEGER DEFAULT NULL, Create_time DATETIME DEFAULT NULL, Last_modify_time DATETIME DEFAULT NULL, PRIMARY KEY ( Collection_id ), UNIQUE KEY Global_id (Global_id), KEY Creator_id ( Creator_id ), KEY parent (Parent_id ), KEY Ctype ( Collection_id, Lead_type ) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -- Table structure for table collection_attributes -- DROP TABLE IF EXISTS collection_attributes; CREATE TABLE collection_attributes ( Object_id BIGINT NOT NULL, Attr_id INT NOT NULL, Attr_def_id INT NOT NULL, Parent_attr INT NOT NULL DEFAULT 0, Clob_id INT NOT NULL DEFAULT 0, PRIMARY KEY (Object_id, Attr_id), UNIQUE KEY (Object_id, Attr_def_id, Attr_id) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -- Table structure for table coll_parent_attributes -- DROP TABLE IF EXISTS coll_parent_attributes; CREATE TABLE coll_parent_attributes ( Object_id BIGINT NOT NULL, Attr_id INT NOT NULL, Parent_attr_id INT NOT NULL, PRIMARY KEY (Object_id, Attr_id, Parent_attr_id) ) ENGINE=InnoDB 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 ( Elem_id BIGINT NOT NULL AUTO_INCREMENT, Object_id BIGINT NOT NULL, Attr_id INT NOT NULL, Element_id INT NOT NULL, Element_value DATE NOT NULL, PRIMARY KEY (Elem_id), KEY Attr_id (Attr_id), KEY Element_value (Element_value) ) ENGINE=InnoDB 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 ( Elem_id BIGINT NOT NULL AUTO_INCREMENT, Object_id BIGINT NOT NULL, Attr_id INT NOT NULL, Element_id INT NOT NULL, Element_value TIME NOT NULL, PRIMARY KEY (Elem_id), KEY Attr_id (Attr_id), KEY Element_value (Element_value) ) ENGINE=InnoDB 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 ( Elem_id BIGINT NOT NULL AUTO_INCREMENT, Object_id BIGINT NOT NULL, Attr_id INT NOT NULL, Element_id INT NOT NULL, Element_value DATETIME NOT NULL, PRIMARY KEY (Elem_id), KEY Attr_id (Attr_id), KEY Element_value (Element_value) ) ENGINE=InnoDB 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 ( Elem_id BIGINT NOT NULL AUTO_INCREMENT, Object_id BIGINT NOT NULL, Attr_id INT NOT NULL, Element_id INT NOT NULL, Element_value FLOAT NOT NULL, PRIMARY KEY (Elem_id), KEY Attr_id (Attr_id), KEY Element_value (Element_value) ) ENGINE=InnoDB 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 ( Elem_id BIGINT NOT NULL AUTO_INCREMENT, Object_id BIGINT NOT NULL, Attr_id INT NOT NULL, Element_id INT NOT NULL, Element_value INT NOT NULL, PRIMARY KEY (Elem_id), KEY Attr_id (Attr_id), KEY Element_value (Element_value) ) ENGINE=InnoDB 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 ( Elem_id BIGINT NOT NULL AUTO_INCREMENT, Object_id BIGINT NOT NULL, Attr_id INT NOT NULL, Element_id INT NOT NULL, Element_value GEOMETRY NOT NULL, PRIMARY KEY (Elem_id), KEY Attr_id (Attr_id), SPATIAL KEY Element_value ( Element_value (32)) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -- Table structure for table mcs_coll_string_elements -- -- The Workspace key is needed for workspace queries DROP TABLE IF EXISTS mcs_coll_string_elements ; CREATE TABLE mcs_coll_string_elements ( Elem_id BIGINT NOT NULL AUTO_INCREMENT, Object_id BIGINT NOT NULL, Attr_id INT NOT NULL, Element_id INT NOT NULL, Element_value VARCHAR(250) NOT NULL, PRIMARY KEY (Elem_id), KEY Attr_id (Attr_id), KEY Workspace (Object_id, Element_id), KEY Element_value (Element_value) ) ENGINE=InnoDB 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 ( Elem_id BIGINT NOT NULL AUTO_INCREMENT, Object_id BIGINT NOT NULL, Attr_id INT NOT NULL, Element_id INT NOT NULL, Element_value MEDIUMTEXT NOT NULL, PRIMARY KEY (Elem_id), KEY Attr_id (Attr_id) ) ENGINE=InnoDB 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 BIGINT NOT NULL AUTO_INCREMENT, Global_id VARCHAR(50) NOT NULL, Collection_id BIGINT NOT NULL DEFAULT 0, Creator_id INTEGER NOT NULL, Last_modifier_id INTEGER DEFAULT NULL, Create_time DATETIME DEFAULT NULL, Last_modify_time DATETIME DEFAULT NULL, PRIMARY KEY (Data_id), UNIQUE KEY Global_id (Global_id), KEY Creator_id (Creator_id), KEY Collection_id (Collection_id) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -- Table structure for table file_attributes -- DROP TABLE IF EXISTS file_attributes ; CREATE TABLE file_attributes ( Object_id BIGINT NOT NULL, Attr_id INT NOT NULL, Attr_def_id INT NOT NULL, Parent_attr INT NOT NULL DEFAULT 0, Clob_id INT NOT NULL DEFAULT 0, PRIMARY KEY (Object_id, Attr_id), UNIQUE KEY (Object_id, Attr_def_id, Attr_id) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -- Table structure for table file_parent_attributes -- DROP TABLE IF EXISTS file_parent_attributes; CREATE TABLE file_parent_attributes ( Object_id BIGINT NOT NULL, Attr_id INT NOT NULL, Parent_attr_id INT NOT NULL, PRIMARY KEY (Object_id, Attr_id, Parent_attr_id) ) ENGINE=InnoDB 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 ( Elem_id BIGINT NOT NULL AUTO_INCREMENT, Object_id BIGINT NOT NULL, Attr_id INT NOT NULL, Element_id INT NOT NULL, Element_value DATE NOT NULL, PRIMARY KEY (Elem_id), KEY Attr_id (Attr_id), KEY Element_value (Element_value) ) ENGINE=InnoDB 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 ( Elem_id BIGINT NOT NULL AUTO_INCREMENT, Object_id BIGINT NOT NULL, Attr_id INT NOT NULL, Element_id INT NOT NULL, Element_value TIME NOT NULL, PRIMARY KEY (Elem_id), KEY Attr_id (Attr_id), KEY Element_value (Element_value) ) ENGINE=InnoDB 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 ( Elem_id BIGINT NOT NULL AUTO_INCREMENT, Object_id BIGINT NOT NULL, Attr_id INT NOT NULL, Element_id INT NOT NULL, Element_value DATETIME NOT NULL, PRIMARY KEY (Elem_id), KEY Attr_id (Attr_id), KEY Element_value (Element_value) ) ENGINE=InnoDB 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 ( Elem_id BIGINT NOT NULL AUTO_INCREMENT, Object_id BIGINT NOT NULL, Attr_id INT NOT NULL, Element_id INT NOT NULL, Element_value FLOAT NOT NULL, PRIMARY KEY (Elem_id), KEY Attr_id (Attr_id), KEY Element_value (Element_value) ) ENGINE=InnoDB 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 ( Elem_id BIGINT NOT NULL AUTO_INCREMENT, Object_id BIGINT NOT NULL, Attr_id INT NOT NULL, Element_id INT NOT NULL, Element_value INT NOT NULL, PRIMARY KEY (Elem_id), KEY Attr_id (Attr_id), KEY Element_value (Element_value) ) ENGINE=InnoDB 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 ( Elem_id BIGINT NOT NULL AUTO_INCREMENT, Object_id BIGINT NOT NULL, Attr_id INT NOT NULL, Element_id INT NOT NULL, Element_value GEOMETRY NOT NULL, PRIMARY KEY (Elem_id), KEY Attr_id (Attr_id), SPATIAL KEY Element_value ( Element_value (32)) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -- Table structure for table mcs_file_string_elements -- -- The Workspace key is needed for workspace queries DROP TABLE IF EXISTS mcs_file_string_elements ; CREATE TABLE mcs_file_string_elements ( Elem_id BIGINT NOT NULL AUTO_INCREMENT, Object_id BIGINT NOT NULL, Attr_id INT NOT NULL, Element_id INT NOT NULL, Element_value VARCHAR(250) NOT NULL, PRIMARY KEY (Elem_id), KEY Attr_id (Attr_id), KEY Workspace (Object_id, Element_id), KEY Element_value (Element_value) ) ENGINE=InnoDB 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 ( Elem_id BIGINT NOT NULL AUTO_INCREMENT, Object_id BIGINT NOT NULL, Attr_id INT NOT NULL, Element_id INT NOT NULL, Element_value MEDIUMTEXT NOT NULL, PRIMARY KEY (Elem_id), KEY Attr_id (Attr_id) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- *************************************** -- Table structure for table mcs_writer -- *************************************** DROP TABLE IF EXISTS mcs_writer; CREATE TABLE mcs_writer ( Writer_id INT NOT NULL AUTO_INCREMENT, Writer_Dn VARCHAR(250) NOT NULL, Writer_replica INT 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 NOT NULL, Last_Modifier_id INT DEFAULT NULL, Create_Time DATETIME DEFAULT NULL, Last_Modify_Time DATETIME DEFAULT NULL, PRIMARY KEY (Writer_id), UNIQUE KEY Dname (Writer_Dn), UNIQUE KEY dn_id (Writer_Dn,Writer_id) ) ENGINE=InnoDB CHARSET=latin1; -- -- Table structure for table 'lead_replica_info' -- DROP TABLE IF EXISTS lead_replica_info; CREATE TABLE lead_replica_info ( Replica_id INT NOT NULL AUTO_INCREMENT, Replica_nickname VARCHAR(255) NOT NULL, 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 INT 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 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 INT DEFAULT NULL, PRIMARY KEY (Replica_id), UNIQUE KEY nickname (Replica_nickname) ) ENGINE=InnoDB CHARSET=latin1; -- -- Table structure for table 'lead_storage_resources' -- DROP TABLE IF EXISTS lead_storage_resources; CREATE TABLE lead_storage_resources ( Resource_id INTEGER NOT NULL AUTO_INCREMENT, -- internal ID Writer_id INTEGER NOT NULL, -- ID from the mcs_writer table Access_url VARCHAR(255) NOT NULL DEFAULT '', Nickname VARCHAR(255) NOT NULL, Resource_global_id VARCHAR(255) NOT NULL DEFAULT '', PRIMARY KEY (Resource_id), UNIQUE KEY nickname(Writer_id, Resource_id, Nickname) ) ENGINE=InnoDB CHARSET=latin1; -- -- 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) ) ENGINE=InnoDB CHARSET=latin1;