Permalink
Switch branches/tags
Nothing to show
Find file Copy path
Fetching contributors…
Cannot retrieve contributors at this time
593 lines (534 sloc) 19.8 KB
-- MySQL Script generated by MySQL Workbench
-- Fri Aug 29 13:02:57 2014
-- Model: New Model Version: 1.0
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';
-- -----------------------------------------------------
-- Schema gdelt
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `gdelt` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ;
USE `gdelt` ;
-- -----------------------------------------------------
-- Table `gdelt`.`SQLDate`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `gdelt`.`SQLDate` (
`SQLDateId` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`SQLDate` DATE NULL,
PRIMARY KEY (`SQLDateId`),
UNIQUE INDEX `SQLDateId_UNIQUE` (`SQLDateId` ASC),
UNIQUE INDEX `SQLDate_UNIQUE` (`SQLDate` ASC))
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `gdelt`.`Date`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `gdelt`.`Date` (
`DateId` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`SQLDateId` INT UNSIGNED NOT NULL,
`MonthYear` INT(6) UNSIGNED NOT NULL,
`Year` YEAR NOT NULL,
`FractionDate` DECIMAL(8,4) UNSIGNED NOT NULL,
UNIQUE INDEX `SQLDATE_UNIQUE` (`SQLDateId` ASC),
PRIMARY KEY (`DateId`),
UNIQUE INDEX `DateId_UNIQUE` (`DateId` ASC),
CONSTRAINT `sqlDate`
FOREIGN KEY (`SQLDateId`)
REFERENCES `gdelt`.`SQLDate` (`SQLDateId`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `gdelt`.`Country`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `gdelt`.`Country` (
`CountryId` SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
`CAMEOCountryCode` VARCHAR(3) NOT NULL,
`Label` VARCHAR(45) NULL,
PRIMARY KEY (`CountryId`))
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `gdelt`.`Religion`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `gdelt`.`Religion` (
`ReligionId` SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
`CAMEOReligionCode` VARCHAR(3) NOT NULL,
`Label` VARCHAR(45) NULL,
PRIMARY KEY (`ReligionId`),
UNIQUE INDEX `ReligionId_UNIQUE` (`ReligionId` ASC),
UNIQUE INDEX `code_UNIQUE` (`CAMEOReligionCode` ASC))
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `gdelt`.`Ethnic`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `gdelt`.`Ethnic` (
`EthnicId` SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
`CAMEOEthnicCode` VARCHAR(3) NOT NULL,
`Label` VARCHAR(45) NULL,
PRIMARY KEY (`EthnicId`),
UNIQUE INDEX `EthnicId_UNIQUE` (`EthnicId` ASC))
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `gdelt`.`KnownGroup`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `gdelt`.`KnownGroup` (
`KnownGroupId` SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
`CAMEOKnownGroupCode` VARCHAR(3) NOT NULL,
`Label` VARCHAR(100) NULL,
PRIMARY KEY (`KnownGroupId`),
UNIQUE INDEX `KnownGroupId_UNIQUE` (`KnownGroupId` ASC),
UNIQUE INDEX `code_UNIQUE` (`CAMEOKnownGroupCode` ASC))
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `gdelt`.`Type`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `gdelt`.`Type` (
`TypeId` SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
`CAMEOTypeCode` VARCHAR(3) NOT NULL,
`Label` VARCHAR(45) NULL,
PRIMARY KEY (`TypeId`),
UNIQUE INDEX `typeId_UNIQUE` (`TypeId` ASC),
UNIQUE INDEX `code_UNIQUE` (`CAMEOTypeCode` ASC))
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `gdelt`.`ActorCode`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `gdelt`.`ActorCode` (
`ActorCodeId` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`ActorCode` VARCHAR(45) NULL,
PRIMARY KEY (`ActorCodeId`))
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `gdelt`.`ActorName`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `gdelt`.`ActorName` (
`ActorNameId` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`Value` VARCHAR(255) NULL,
PRIMARY KEY (`ActorNameId`))
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `gdelt`.`Actor`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `gdelt`.`Actor` (
`ActorId` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`ActorCodeId` INT UNSIGNED NULL,
`ActorNameId` INT UNSIGNED NULL,
`CountryId` SMALLINT UNSIGNED NULL,
`KnownGroupId` SMALLINT UNSIGNED NULL,
`EthnicId` SMALLINT UNSIGNED NULL,
`Religion1Id` SMALLINT UNSIGNED NULL,
`Religion2Id` SMALLINT UNSIGNED NULL,
`Type1Id` SMALLINT UNSIGNED NULL,
`Type2Id` SMALLINT UNSIGNED NULL,
`Type3Id` SMALLINT UNSIGNED NULL,
PRIMARY KEY (`ActorId`),
UNIQUE INDEX `ActorId_UNIQUE` (`ActorId` ASC),
INDEX `countryCode_idx` (`CountryId` ASC),
INDEX `religion1_idx` (`Religion1Id` ASC),
INDEX `religion2_idx` (`Religion2Id` ASC),
INDEX `ethnic_idx` (`EthnicId` ASC),
INDEX `knownGroup_idx` (`KnownGroupId` ASC),
INDEX `type1_idx` (`Type1Id` ASC),
INDEX `type2_idx` (`Type2Id` ASC),
INDEX `type3_idx` (`Type3Id` ASC),
INDEX `actorId_idx` (`ActorCodeId` ASC),
INDEX `actorName_idx` (`ActorNameId` ASC),
CONSTRAINT `countryCode`
FOREIGN KEY (`CountryId`)
REFERENCES `gdelt`.`Country` (`CountryId`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `religion1`
FOREIGN KEY (`Religion1Id`)
REFERENCES `gdelt`.`Religion` (`ReligionId`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `religion2`
FOREIGN KEY (`Religion2Id`)
REFERENCES `gdelt`.`Religion` (`ReligionId`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `ethnic`
FOREIGN KEY (`EthnicId`)
REFERENCES `gdelt`.`Ethnic` (`EthnicId`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `knownGroup`
FOREIGN KEY (`KnownGroupId`)
REFERENCES `gdelt`.`KnownGroup` (`KnownGroupId`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `type1`
FOREIGN KEY (`Type1Id`)
REFERENCES `gdelt`.`Type` (`TypeId`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `type2`
FOREIGN KEY (`Type2Id`)
REFERENCES `gdelt`.`Type` (`TypeId`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `type3`
FOREIGN KEY (`Type3Id`)
REFERENCES `gdelt`.`Type` (`TypeId`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `actorId`
FOREIGN KEY (`ActorCodeId`)
REFERENCES `gdelt`.`ActorCode` (`ActorCodeId`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `actorName`
FOREIGN KEY (`ActorNameId`)
REFERENCES `gdelt`.`ActorName` (`ActorNameId`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `gdelt`.`EventCode`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `gdelt`.`EventCode` (
`EventCodeId` SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
`CAMEOEventCode` VARCHAR(4) NOT NULL,
`Label` VARCHAR(255) NULL,
PRIMARY KEY (`EventCodeId`),
UNIQUE INDEX `EventCodeId_UNIQUE` (`EventCodeId` ASC),
UNIQUE INDEX `code_UNIQUE` (`CAMEOEventCode` ASC))
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `gdelt`.`QuadClass`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `gdelt`.`QuadClass` (
`QuadClassId` TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
`QuadClass` TINYINT UNSIGNED NOT NULL,
`Label` VARCHAR(45) NOT NULL,
PRIMARY KEY (`QuadClassId`),
UNIQUE INDEX `QuadClassId_UNIQUE` (`QuadClassId` ASC),
UNIQUE INDEX `QuadClass_UNIQUE` (`QuadClass` ASC),
UNIQUE INDEX `Label_UNIQUE` (`Label` ASC))
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `gdelt`.`Event`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `gdelt`.`Event` (
`EventId` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`IsRootEvent` TINYINT(1) NULL,
`EventCodeId` SMALLINT UNSIGNED NULL,
`BaseEventCodeId` SMALLINT UNSIGNED NULL,
`RootEventCodeId` SMALLINT UNSIGNED NULL,
`QuadClassId` TINYINT UNSIGNED NULL,
PRIMARY KEY (`EventId`),
UNIQUE INDEX `EventId_UNIQUE` (`EventId` ASC),
INDEX `eventCode_idx` (`EventCodeId` ASC),
INDEX `baseCode_idx` (`BaseEventCodeId` ASC),
INDEX `rootCode_idx` (`RootEventCodeId` ASC),
INDEX `quadClass_idx` (`QuadClassId` ASC),
CONSTRAINT `code`
FOREIGN KEY (`EventCodeId`)
REFERENCES `gdelt`.`EventCode` (`EventCodeId`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `baseCode`
FOREIGN KEY (`BaseEventCodeId`)
REFERENCES `gdelt`.`EventCode` (`EventCodeId`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `rootCode`
FOREIGN KEY (`RootEventCodeId`)
REFERENCES `gdelt`.`EventCode` (`EventCodeId`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `quadClass`
FOREIGN KEY (`QuadClassId`)
REFERENCES `gdelt`.`QuadClass` (`QuadClassId`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `gdelt`.`NumMentions`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `gdelt`.`NumMentions` (
`NumMentionsId` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`Value` INT UNSIGNED NULL,
PRIMARY KEY (`NumMentionsId`),
UNIQUE INDEX `NumMentionsIds_UNIQUE` (`NumMentionsId` ASC),
UNIQUE INDEX `Value_UNIQUE` (`Value` ASC))
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `gdelt`.`NumSources`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `gdelt`.`NumSources` (
`NumSourcesId` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`Value` INT UNSIGNED NULL,
PRIMARY KEY (`NumSourcesId`),
UNIQUE INDEX `NumSourcesId_UNIQUE` (`NumSourcesId` ASC),
UNIQUE INDEX `Value_UNIQUE` (`Value` ASC))
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `gdelt`.`NumArticles`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `gdelt`.`NumArticles` (
`NumArticlesId` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`Value` INT UNSIGNED NULL,
PRIMARY KEY (`NumArticlesId`),
UNIQUE INDEX `NumArticlesId_UNIQUE` (`NumArticlesId` ASC),
UNIQUE INDEX `Value_UNIQUE` (`Value` ASC))
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `gdelt`.`AvgTone`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `gdelt`.`AvgTone` (
`AvgToneId` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`Value` DECIMAL(6,2) NULL,
PRIMARY KEY (`AvgToneId`),
UNIQUE INDEX `AvgToneId_UNIQUE` (`AvgToneId` ASC),
UNIQUE INDEX `Value_UNIQUE` (`Value` ASC))
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `gdelt`.`Media`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `gdelt`.`Media` (
`MediaId` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`NumMentionsId` INT UNSIGNED NULL,
`NumSourcesId` INT UNSIGNED NULL,
`NumArticlesId` INT UNSIGNED NULL,
`AvgToneId` INT UNSIGNED NULL,
PRIMARY KEY (`MediaId`),
UNIQUE INDEX `MediaId_UNIQUE` (`MediaId` ASC),
INDEX `numMentions_idx` (`NumMentionsId` ASC),
INDEX `numSources_idx` (`NumSourcesId` ASC),
INDEX `numArticles_idx` (`NumArticlesId` ASC),
INDEX `avgTone_idx` (`AvgToneId` ASC),
CONSTRAINT `numMentions`
FOREIGN KEY (`NumMentionsId`)
REFERENCES `gdelt`.`NumMentions` (`NumMentionsId`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `numSources`
FOREIGN KEY (`NumSourcesId`)
REFERENCES `gdelt`.`NumSources` (`NumSourcesId`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `numArticles`
FOREIGN KEY (`NumArticlesId`)
REFERENCES `gdelt`.`NumArticles` (`NumArticlesId`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `avgTone`
FOREIGN KEY (`AvgToneId`)
REFERENCES `gdelt`.`AvgTone` (`AvgToneId`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `gdelt`.`GeoName`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `gdelt`.`GeoName` (
`GeoNameId` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`Label` VARCHAR(255) NULL,
PRIMARY KEY (`GeoNameId`),
UNIQUE INDEX `geoNameId_UNIQUE` (`GeoNameId` ASC))
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `gdelt`.`GeoCountry`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `gdelt`.`GeoCountry` (
`GeoCountryId` SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
`Code` CHAR(2) NULL,
PRIMARY KEY (`GeoCountryId`),
UNIQUE INDEX `geoCountryCodeId_UNIQUE` (`GeoCountryId` ASC))
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `gdelt`.`ADM1Code`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `gdelt`.`ADM1Code` (
`AdmCodeId` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`Code` VARCHAR(4) NULL,
PRIMARY KEY (`AdmCodeId`))
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `gdelt`.`Location`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `gdelt`.`Location` (
`LocationId` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`Latitude` DECIMAL(7,4) NULL,
`Longitude` DECIMAL(7,4) NULL,
PRIMARY KEY (`LocationId`),
UNIQUE INDEX `LocationId_UNIQUE` (`LocationId` ASC))
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `gdelt`.`GeoType`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `gdelt`.`GeoType` (
`GeoTypeId` TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
`GeoType` TINYINT UNSIGNED NOT NULL,
`Label` VARCHAR(45) NULL,
PRIMARY KEY (`GeoTypeId`),
UNIQUE INDEX `GeoTypeId_UNIQUE` (`GeoTypeId` ASC),
UNIQUE INDEX `GeoType_UNIQUE` (`GeoType` ASC))
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `gdelt`.`GeoFeature`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `gdelt`.`GeoFeature` (
`GeoFeatureId` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`Value` VARCHAR(20) NULL,
PRIMARY KEY (`GeoFeatureId`),
UNIQUE INDEX `GeoFeatureId_UNIQUE` (`GeoFeatureId` ASC),
UNIQUE INDEX `value_UNIQUE` (`Value` ASC))
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `gdelt`.`Geo`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `gdelt`.`Geo` (
`GeoId` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`GeoTypeId` TINYINT UNSIGNED NULL,
`GeoNameId` INT UNSIGNED NULL,
`GeoCountryId` SMALLINT UNSIGNED NULL,
`ADM1CodeId` INT UNSIGNED NULL,
`LocationId` INT UNSIGNED NULL,
`FeatureId` INT UNSIGNED NULL,
PRIMARY KEY (`GeoId`),
UNIQUE INDEX `GeoId_UNIQUE` (`GeoId` ASC),
INDEX `geoName_idx` (`GeoNameId` ASC),
INDEX `countryId_idx` (`GeoCountryId` ASC),
INDEX `adm1Code_idx` (`ADM1CodeId` ASC),
INDEX `location_idx` (`LocationId` ASC),
INDEX `type_idx` (`GeoTypeId` ASC),
INDEX `feature_idx` (`FeatureId` ASC),
CONSTRAINT `geoName`
FOREIGN KEY (`GeoNameId`)
REFERENCES `gdelt`.`GeoName` (`GeoNameId`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `countryId`
FOREIGN KEY (`GeoCountryId`)
REFERENCES `gdelt`.`GeoCountry` (`GeoCountryId`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `adm1Code`
FOREIGN KEY (`ADM1CodeId`)
REFERENCES `gdelt`.`ADM1Code` (`AdmCodeId`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `location`
FOREIGN KEY (`LocationId`)
REFERENCES `gdelt`.`Location` (`LocationId`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `type`
FOREIGN KEY (`GeoTypeId`)
REFERENCES `gdelt`.`GeoType` (`GeoTypeId`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `feature`
FOREIGN KEY (`FeatureId`)
REFERENCES `gdelt`.`GeoFeature` (`GeoFeatureId`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `gdelt`.`Source`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `gdelt`.`Source` (
`SourceId` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`URL` TEXT NULL,
PRIMARY KEY (`SourceId`))
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `gdelt`.`GdeltMain`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `gdelt`.`GdeltMain` (
`Id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`GLOBALEVENTID` BIGINT UNSIGNED NOT NULL,
`DateId` INT UNSIGNED NOT NULL,
`Actor1Id` INT UNSIGNED NOT NULL,
`Actor2Id` INT UNSIGNED NOT NULL,
`EventId` INT UNSIGNED NOT NULL,
`MediaId` INT UNSIGNED NOT NULL,
`Actor1GeoId` INT UNSIGNED NOT NULL,
`Actor2GeoId` INT UNSIGNED NOT NULL,
`ActionGeoId` INT UNSIGNED NOT NULL,
`AddedSQLDateId` INT UNSIGNED NOT NULL,
`SourceId` INT UNSIGNED NOT NULL,
INDEX `Actor.Actor1_idx` (`Actor1Id` ASC),
INDEX `actor2_idx` (`Actor2Id` ASC),
INDEX `event_idx` (`EventId` ASC),
INDEX `media_idx` (`MediaId` ASC),
INDEX `actor1Geo_idx` (`Actor1GeoId` ASC),
INDEX `actor2Geo_idx` (`Actor2GeoId` ASC),
INDEX `actionGeo_idx` (`ActionGeoId` ASC),
INDEX `sourceUrl_idx` (`SourceId` ASC),
INDEX `date_idx` (`DateId` ASC),
INDEX `dateAdded_idx` (`AddedSQLDateId` ASC),
PRIMARY KEY (`Id`),
UNIQUE INDEX `Id_UNIQUE` (`Id` ASC),
CONSTRAINT `actor1`
FOREIGN KEY (`Actor1Id`)
REFERENCES `gdelt`.`Actor` (`ActorId`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `actor2`
FOREIGN KEY (`Actor2Id`)
REFERENCES `gdelt`.`Actor` (`ActorId`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `event`
FOREIGN KEY (`EventId`)
REFERENCES `gdelt`.`Event` (`EventId`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `media`
FOREIGN KEY (`MediaId`)
REFERENCES `gdelt`.`Media` (`MediaId`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `actor1Geo`
FOREIGN KEY (`Actor1GeoId`)
REFERENCES `gdelt`.`Geo` (`GeoId`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `actor2Geo`
FOREIGN KEY (`Actor2GeoId`)
REFERENCES `gdelt`.`Geo` (`GeoId`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `actionGeo`
FOREIGN KEY (`ActionGeoId`)
REFERENCES `gdelt`.`Geo` (`GeoId`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `dateAdded`
FOREIGN KEY (`AddedSQLDateId`)
REFERENCES `gdelt`.`SQLDate` (`SQLDateId`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `sourceUrl`
FOREIGN KEY (`SourceId`)
REFERENCES `gdelt`.`Source` (`SourceId`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `date`
FOREIGN KEY (`DateId`)
REFERENCES `gdelt`.`Date` (`DateId`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `gdelt`.`GoldsteinScale`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `gdelt`.`GoldsteinScale` (
`GoldsteinScaleId` SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
`CAMEOEventCode` VARCHAR(4) NOT NULL,
`Value` DECIMAL(4,2) NOT NULL,
PRIMARY KEY (`GoldsteinScaleId`),
UNIQUE INDEX `goldsteinScaleId_UNIQUE` (`GoldsteinScaleId` ASC),
UNIQUE INDEX `CAMEOEventCode_UNIQUE` (`CAMEOEventCode` ASC),
CONSTRAINT `cameoEventCode`
FOREIGN KEY (`CAMEOEventCode`)
REFERENCES `gdelt`.`EventCode` (`CAMEOEventCode`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;