SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT;
SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS;
SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION;
SET NAMES utf8;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE=NO_AUTO_VALUE_ON_ZERO */;


CREATE DATABASE /*!32312 IF NOT EXISTS*/ `ka4`;
USE `ka4`;
CREATE TABLE `aliasses` (
  `Keyword` varchar(25) NOT NULL default '',
  `Alias` varchar(25) NOT NULL default '',
  PRIMARY KEY  (`Keyword`,`Alias`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE TABLE `aspectrelations` (
  `RelationType` int(11) NOT NULL default '0',
  `CardinalitySource` int(11) NOT NULL default '1',
  `CardinalityTarget` int(11) NOT NULL default '1',
  `name` varchar(35) NOT NULL default '',
  `RoleId` int(11) NOT NULL default '0',
  `AspectId` int(11) NOT NULL default '0',
  `RelatedAspectId` int(11) NOT NULL default '0',
  PRIMARY KEY  (`AspectId`,`RelatedAspectId`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO `aspectrelations` (`RelationType`,`CardinalitySource`,`CardinalityTarget`,`name`,`RoleId`,`AspectId`,`RelatedAspectId`) VALUES (0,1,1,'has',1,1,2),(0,1,1,'has',1,1,3),(0,1,1,'hasa',1,2,6),(0,1,1,'hasa',1,2,7),(0,1,1,'hasa',1,2,8),(0,1,1,'hasa',1,2,9),(0,1,1,'hasa',1,2,10),(0,1,1,'hasa',1,2,11),(0,1,1,'hasa',1,2,12),(0,1,1,'hasa',1,2,13),(0,1,1,'isa',1,3,15),(0,1,1,'isa',1,3,16),(0,1,1,'isa',1,3,17),(0,1,1,'isa',1,12,18),(0,1,1,'isa',2,18,19),(0,1,1,'isa',1,18,20),(0,1,1,'isa',2,19,21),(0,1,1,'isa',3,20,22),(0,1,1,'isa',1,20,23),(0,1,1,'relationinternal20050420',1,23,26),(0,1,1,'relationinternal20050420',1,23,27);
CREATE TABLE `aspects` (
  `AspectId` int(11) NOT NULL auto_increment,
  `Description` varchar(100) default '',
  `Name` varchar(30) NOT NULL default '',
  PRIMARY KEY  (`AspectId`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO `aspects` (`AspectId`,`Description`,`Name`) VALUES (1,NULL,'universe'),(2,NULL,'geologicalconcept'),(3,NULL,'geologicalpresentation'),(6,'no description','geologicalrelation'),(7,'no description','earthmaterial'),(8,'no description','geologicalprocess'),(9,'no description','geologicalstructure'),(10,'no description','geologicalevent'),(11,'no description','fosil'),(12,'no description','geologicalproperty'),(13,'no description','geologicalunit'),(15,'no description','geologicalvocabulary'),(16,'no description','geologicalportrayal'),(17,'no description','geologicalportrayalcomponent'),(18,'no description','geologicalage'),(19,'no description','agedate'),(20,'no description','ageinterval'),(21,'no description','radiometricage'),(22,'no description','unnamedage'),(23,'no description','namedage'),(27,'no description','stratigraphicage'),(26,'no description','biostratigraphicage');
CREATE TABLE `binaryvalues` (
  `OccurrenceId` int(11) NOT NULL default '0',
  `Value` blob NOT NULL,
  PRIMARY KEY  (`OccurrenceId`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE TABLE `descriptions` (
  `AspectId` int(11) NOT NULL default '0',
  `Name` varchar(30) default '',
  `ValueType` smallint(6) NOT NULL default '0',
  `Unit` varchar(25) default '',
  PRIMARY KEY  (`AspectId`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO `descriptions` (`AspectId`,`Name`,`ValueType`,`Unit`) VALUES (27,'',1,'none'),(26,'',1,'none');
CREATE TABLE `doublevalues` (
  `OccurrenceId` int(11) NOT NULL default '0',
  `Value` double NOT NULL default '0',
  PRIMARY KEY  (`OccurrenceId`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE TABLE `gridcoveragevalues` (
  `CoordinateSystem` varchar(100) default '',
  `RelatedAspectId` int(11) NOT NULL default '0',
  `OccurrenceId` int(11) NOT NULL default '0',
  `Value` varchar(255) NOT NULL default '',
  `Envelop` linestring default NULL,
  PRIMARY KEY  (`OccurrenceId`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE TABLE `integervalues` (
  `OccurrenceId` int(11) NOT NULL default '0',
  `Value` int(11) NOT NULL default '0',
  PRIMARY KEY  (`OccurrenceId`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE TABLE `keywords` (
  `Keyword` varchar(25) NOT NULL default '',
  `Description` varchar(100) default '',
  PRIMARY KEY  (`Keyword`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE TABLE `lookupentries` (
  `AspectId` int(11) NOT NULL default '0',
  `LookupId` int(11) NOT NULL default '0',
  PRIMARY KEY  (`AspectId`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE TABLE `lookupvalues` (
  `LookupID` int(11) NOT NULL auto_increment,
  `LookUpValue` varchar(100) default '',
  PRIMARY KEY  (`LookupID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE TABLE `occurrencekeywords` (
  `OccurrenceId` int(11) NOT NULL default '0',
  `Keyword` varchar(30) NOT NULL default '',
  PRIMARY KEY  (`OccurrenceId`,`Keyword`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE TABLE `occurrencerelations` (
  `OccurrenceId` int(11) NOT NULL default '0',
  `RelatedOccurrenceId` int(11) NOT NULL default '0',
  PRIMARY KEY  (`OccurrenceId`,`RelatedOccurrenceId`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO `occurrencerelations` (`OccurrenceId`,`RelatedOccurrenceId`) VALUES (1,2);
CREATE TABLE `occurrences` (
  `OccurrenceId` int(11) NOT NULL auto_increment,
  `AspectId` int(11) NOT NULL default '0',
  `OccurrenceTime` datetime default '0000-00-00 00:00:00',
  `Notes` longtext,
  PRIMARY KEY  (`OccurrenceId`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE TABLE `occurrencevalues` (
  `OccurrenceId` int(11) NOT NULL default '0',
  `QualifierCode` tinyint(4) NOT NULL default '0',
  `UncertaintyCode` tinyint(4) NOT NULL default '0',
  PRIMARY KEY  (`OccurrenceId`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE TABLE `perspectiveroles` (
  `RoleId` int(11) NOT NULL default '0',
  `PerspectiveId` int(11) NOT NULL default '0',
  PRIMARY KEY  (`RoleId`,`PerspectiveId`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO `perspectiveroles` (`RoleId`,`PerspectiveId`) VALUES (1,2),(1,4),(1,5),(2,4),(2,5),(3,5),(3,7);
CREATE TABLE `perspectives` (
  `type` varchar(25) NOT NULL default '',
  `PerspectiveID` int(11) NOT NULL auto_increment,
  `name` varchar(35) NOT NULL default '',
  PRIMARY KEY  (`PerspectiveID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO `perspectives` (`type`,`PerspectiveID`,`name`) VALUES ('Acquisition method',2,'Fieldwork'),('Acquisition method',3,'Lab work'),('Acquisition method',4,'Literature'),('Discipline',5,'Geology'),('Discipline',6,'Biology'),('Function',7,'Researcher'),('Function',8,'Student');
CREATE TABLE `pointgeometryvalues` (
  `occurrenceid` int(10) unsigned NOT NULL default '0',
  `value` point NOT NULL default '',
  PRIMARY KEY  (`occurrenceid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE TABLE `qualifiercodes` (
  `QualifierCode` tinyint(4) NOT NULL default '0',
  `Description` varchar(100) NOT NULL default '',
  PRIMARY KEY  (`QualifierCode`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE TABLE `references` (
  `OccurrenceId` int(11) NOT NULL default '0',
  `ReferenceId` int(11) NOT NULL default '0',
  PRIMARY KEY  (`OccurrenceId`,`ReferenceId`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE TABLE `referencescontent` (
  `ReferenceId` int(11) NOT NULL default '0',
  `ReferenceTypeId` smallint(6) NOT NULL default '0',
  `BinaryContent` blob,
  `StringContent` longtext,
  `Description` varchar(100) default '',
  PRIMARY KEY  (`ReferenceId`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE TABLE `referencetype` (
  `ReferenceTypeId` smallint(6) NOT NULL default '0',
  `Description` varchar(100) default '',
  PRIMARY KEY  (`ReferenceTypeId`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE TABLE `roles` (
  `roleid` int(11) NOT NULL auto_increment,
  PRIMARY KEY  (`roleid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO `roles` (`roleid`) VALUES (1),(2),(3);
CREATE TABLE `stringvalues` (
  `OccurrenceId` int(11) NOT NULL default '0',
  `Value` varchar(100) NOT NULL default '',
  PRIMARY KEY  (`OccurrenceId`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE TABLE `uncertaintycodes` (
  `UncertaintyCode` tinyint(4) NOT NULL default '0',
  `Description` varchar(100) default ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE TABLE `valuetypes` (
  `ValueTypeId` varchar(100) NOT NULL default '',
  `Description` varchar(100) default '',
  PRIMARY KEY  (`ValueTypeId`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO `valuetypes` (`ValueTypeId`,`Description`) VALUES ('0','Double'),('1','String'),('2','Lookup'),('3','Binary'),('4','PolygonGeometry'),('5','PointGeometry'),('6','GridCoverage'),('7','Integer');
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT;
SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS;
SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
