initdb.sql

CREATE EXTENSION IF NOT EXISTS multicorn;

CREATE SERVER IF NOT EXISTS gdelt_fdw FOREIGN DATA WRAPPER multicorn OPTIONS (
  wrapper 'gdelt_fdw.GdeltForeignDataWrapper'
);

CREATE SERVER IF NOT EXISTS api_fdw FOREIGN DATA WRAPPER multicorn OPTIONS (
  wrapper 'api_fdw.ApiForeignDataWrapper'
);

CREATE FOREIGN TABLE IF NOT EXISTS gdeltv1_events (
  GLOBALEVENTID integer,
  SQLDATE integer,
  MonthYear integer,
  Year integer,
  FractionDate float,
  Actor1Code varchar,
  Actor1Name varchar,
  Actor1CountryCode varchar,
  Actor1KnownGroupCode varchar,
  Actor1EthnicCode varchar,
  Actor1Religion1Code varchar,
  Actor1Religion2Code varchar,
  Actor1Type1Code varchar,
  Actor1Type2Code varchar,
  Actor1Type3Code varchar,
  Actor2Code varchar,
  Actor2Name varchar,
  Actor2CountryCode varchar,
  Actor2KnownGroupCode varchar,
  Actor2EthnicCode varchar,
  Actor2Religion1Code varchar,
  Actor2Religion2Code varchar,
  Actor2Type1Code varchar,
  Actor2Type2Code varchar,
  Actor2Type3Code varchar,
  IsRootEvent integer,
  EventCode varchar,
  EventBaseCode varchar,
  EventRootCode varchar,
  QuadClass integer,
  GoldsteinScale float,
  NumMentions integer,
  NumSources integer,
  NumArticles integer,
  AvgTone float,
  Actor1Geo_Type integer,
  Actor1Geo_FullName varchar,
  Actor1Geo_CountryCode varchar,
  Actor1Geo_ADM1Code varchar,
  Actor1Geo_Lat float,
  Actor1Geo_Long float,
  Actor1Geo_FeatureID varchar,
  Actor2Geo_Type integer,
  Actor2Geo_FullName varchar,
  Actor2Geo_CountryCode varchar,
  Actor2Geo_ADM1Code varchar,
  Actor2Geo_Lat float,
  Actor2Geo_Long float,
  Actor2Geo_FeatureID varchar,
  ActionGeo_Type integer,
  ActionGeo_FullName varchar,
  ActionGeo_CountryCode varchar,
  ActionGeo_ADM1Code varchar,
  ActionGeo_Lat float,
  ActionGeo_Long float,
  ActionGeo_FeatureID varchar,
  DATEADDED bigint,
  SOURCEURL varchar
) SERVER gdelt_fdw OPTIONS (
  table 'gdeltv1_events'
);

CREATE FOREIGN TABLE IF NOT EXISTS gdeltv2_events (
  GLOBALEVENTID integer,
  SQLDATE integer,
  MonthYear integer,
  Year integer,
  FractionDate float,
  Actor1Code varchar,
  Actor1Name varchar,
  Actor1CountryCode varchar,
  Actor1KnownGroupCode varchar,
  Actor1EthnicCode varchar,
  Actor1Religion1Code varchar,
  Actor1Religion2Code varchar,
  Actor1Type1Code varchar,
  Actor1Type2Code varchar,
  Actor1Type3Code varchar,
  Actor2Code varchar,
  Actor2Name varchar,
  Actor2CountryCode varchar,
  Actor2KnownGroupCode varchar,
  Actor2EthnicCode varchar,
  Actor2Religion1Code varchar,
  Actor2Religion2Code varchar,
  Actor2Type1Code varchar,
  Actor2Type2Code varchar,
  Actor2Type3Code varchar,
  IsRootEvent integer,
  EventCode varchar,
  EventBaseCode varchar,
  EventRootCode varchar,
  QuadClass integer,
  GoldsteinScale float,
  NumMentions integer,
  NumSources integer,
  NumArticles integer,
  AvgTone float,
  Actor1Geo_Type integer,
  Actor1Geo_FullName varchar,
  Actor1Geo_CountryCode varchar,
  Actor1Geo_ADM1Code varchar,
  Actor1Geo_ADM2Code varchar,
  Actor1Geo_Lat float,
  Actor1Geo_Long float,
  Actor1Geo_FeatureID varchar,
  Actor2Geo_Type integer,
  Actor2Geo_FullName varchar,
  Actor2Geo_CountryCode varchar,
  Actor2Geo_ADM1Code varchar,
  Actor2Geo_ADM2Code varchar,
  Actor2Geo_Lat float,
  Actor2Geo_Long float,
  Actor2Geo_FeatureID varchar,
  ActionGeo_Type integer,
  ActionGeo_FullName varchar,
  ActionGeo_CountryCode varchar,
  ActionGeo_ADM1Code varchar,
  ActionGeo_ADM2Code varchar,
  ActionGeo_Lat float,
  ActionGeo_Long float,
  ActionGeo_FeatureID varchar,
  DATEADDED bigint,
  SOURCEURL varchar
) SERVER gdelt_fdw OPTIONS (
  table 'gdeltv2_events'
);

CREATE FOREIGN TABLE IF NOT EXISTS gdeltv2_gkg (
  GKGRECORDID varchar,
  DATE bigint,
  SourceCollectionIdentifier integer,
  SourceCommonName varchar,
  DocumentIdentifier varchar,
  Counts varchar,
  V2Counts varchar,
  Themes varchar,
  V2Themes varchar,
  Locations varchar,
  V2Locations varchar,
  Persons varchar,
  V2Persons varchar,
  Organizations varchar,
  V2Organizations varchar,
  V2Tone varchar,
  Dates varchar,
  GCAM varchar,
  SharingImage varchar,
  RelatedImages varchar,
  SocialImageEmbeds varchar,
  SocialVideoEmbeds varchar,
  Quotations varchar,
  AllNames varchar,
  Amounts varchar,
  TranslationInfo varchar,
  Extras varchar
) SERVER gdelt_fdw OPTIONS (
  table 'gdeltv2_gkg'
);

CREATE FOREIGN TABLE IF NOT EXISTS gdeltv1_events_api (
  GLOBALEVENTID integer,
  SQLDATE integer,
  MonthYear integer,
  Year integer,
  FractionDate float,
  Actor1Code varchar,
  Actor1Name varchar,
  Actor1CountryCode varchar,
  Actor1KnownGroupCode varchar,
  Actor1EthnicCode varchar,
  Actor1Religion1Code varchar,
  Actor1Religion2Code varchar,
  Actor1Type1Code varchar,
  Actor1Type2Code varchar,
  Actor1Type3Code varchar,
  Actor2Code varchar,
  Actor2Name varchar,
  Actor2CountryCode varchar,
  Actor2KnownGroupCode varchar,
  Actor2EthnicCode varchar,
  Actor2Religion1Code varchar,
  Actor2Religion2Code varchar,
  Actor2Type1Code varchar,
  Actor2Type2Code varchar,
  Actor2Type3Code varchar,
  IsRootEvent integer,
  EventCode varchar,
  EventBaseCode varchar,
  EventRootCode varchar,
  QuadClass integer,
  GoldsteinScale float,
  NumMentions integer,
  NumSources integer,
  NumArticles integer,
  AvgTone float,
  Actor1Geo_Type integer,
  Actor1Geo_FullName varchar,
  Actor1Geo_CountryCode varchar,
  Actor1Geo_ADM1Code varchar,
  Actor1Geo_Lat float,
  Actor1Geo_Long float,
  Actor1Geo_FeatureID varchar,
  Actor2Geo_Type integer,
  Actor2Geo_FullName varchar,
  Actor2Geo_CountryCode varchar,
  Actor2Geo_ADM1Code varchar,
  Actor2Geo_Lat float,
  Actor2Geo_Long float,
  Actor2Geo_FeatureID varchar,
  ActionGeo_Type integer,
  ActionGeo_FullName varchar,
  ActionGeo_CountryCode varchar,
  ActionGeo_ADM1Code varchar,
  ActionGeo_Lat float,
  ActionGeo_Long float,
  ActionGeo_FeatureID varchar,
  DATEADDED bigint,
  SOURCEURL varchar
) SERVER api_fdw OPTIONS (
  url 'http://app/gdeltv1_events',
  table 'gdeltv1_events'
);

CREATE FOREIGN TABLE IF NOT EXISTS gdeltv2_events_api (
  GLOBALEVENTID integer,
  SQLDATE integer,
  MonthYear integer,
  Year integer,
  FractionDate float,
  Actor1Code varchar,
  Actor1Name varchar,
  Actor1CountryCode varchar,
  Actor1KnownGroupCode varchar,
  Actor1EthnicCode varchar,
  Actor1Religion1Code varchar,
  Actor1Religion2Code varchar,
  Actor1Type1Code varchar,
  Actor1Type2Code varchar,
  Actor1Type3Code varchar,
  Actor2Code varchar,
  Actor2Name varchar,
  Actor2CountryCode varchar,
  Actor2KnownGroupCode varchar,
  Actor2EthnicCode varchar,
  Actor2Religion1Code varchar,
  Actor2Religion2Code varchar,
  Actor2Type1Code varchar,
  Actor2Type2Code varchar,
  Actor2Type3Code varchar,
  IsRootEvent integer,
  EventCode varchar,
  EventBaseCode varchar,
  EventRootCode varchar,
  QuadClass integer,
  GoldsteinScale float,
  NumMentions integer,
  NumSources integer,
  NumArticles integer,
  AvgTone float,
  Actor1Geo_Type integer,
  Actor1Geo_FullName varchar,
  Actor1Geo_CountryCode varchar,
  Actor1Geo_ADM1Code varchar,
  Actor1Geo_ADM2Code varchar,
  Actor1Geo_Lat float,
  Actor1Geo_Long float,
  Actor1Geo_FeatureID varchar,
  Actor2Geo_Type integer,
  Actor2Geo_FullName varchar,
  Actor2Geo_CountryCode varchar,
  Actor2Geo_ADM1Code varchar,
  Actor2Geo_ADM2Code varchar,
  Actor2Geo_Lat float,
  Actor2Geo_Long float,
  Actor2Geo_FeatureID varchar,
  ActionGeo_Type integer,
  ActionGeo_FullName varchar,
  ActionGeo_CountryCode varchar,
  ActionGeo_ADM1Code varchar,
  ActionGeo_ADM2Code varchar,
  ActionGeo_Lat float,
  ActionGeo_Long float,
  ActionGeo_FeatureID varchar,
  DATEADDED bigint,
  SOURCEURL varchar
) SERVER api_fdw OPTIONS (
  url 'http://app/gdeltv2_events',
  table 'gdeltv2_events'
);

CREATE FOREIGN TABLE IF NOT EXISTS gdeltv2_gkg_api (
  GKGRECORDID varchar,
  DATE bigint,
  SourceCollectionIdentifier integer,
  SourceCommonName varchar,
  DocumentIdentifier varchar,
  Counts varchar,
  V2Counts varchar,
  Themes varchar,
  V2Themes varchar,
  Locations varchar,
  V2Locations varchar,
  Persons varchar,
  V2Persons varchar,
  Organizations varchar,
  V2Organizations varchar,
  V2Tone varchar,
  Dates varchar,
  GCAM varchar,
  SharingImage varchar,
  RelatedImages varchar,
  SocialImageEmbeds varchar,
  SocialVideoEmbeds varchar,
  Quotations varchar,
  AllNames varchar,
  Amounts varchar,
  TranslationInfo varchar,
  Extras varchar
) SERVER api_fdw OPTIONS (
  url 'http://app/gdeltv2_gkg',
  table 'gdeltv2_gkg'
);