186 lines
4.7 KiB
SQL
186 lines
4.7 KiB
SQL
SET client_encoding = 'UTF8';
|
|
SET standard_conforming_strings = on;
|
|
|
|
|
|
-- MAIN ENTITIES --
|
|
|
|
CREATE TABLE document (
|
|
id text PRIMARY KEY,
|
|
title text,
|
|
abstract text,
|
|
doctype text,
|
|
pmcid text,
|
|
repository text,
|
|
doi text,
|
|
pubyear text
|
|
);
|
|
|
|
CREATE TABLE project (
|
|
id text PRIMARY KEY,
|
|
grantid text,
|
|
title text,
|
|
acronym text,
|
|
funder text,
|
|
fundinglevel0 text,
|
|
fundinglevel1 text,
|
|
fundinglevel2 text,
|
|
callid text,
|
|
rcn text,
|
|
startdate text,
|
|
enddate text,
|
|
websiteurl text,
|
|
keywords text,
|
|
contracttype text,
|
|
ec_sc39 boolean,
|
|
oa_mandate_for_publications boolean,
|
|
ec_article29_3 boolean
|
|
);
|
|
|
|
CREATE TABLE author (
|
|
id text PRIMARY KEY,
|
|
firstname text,
|
|
lastname text,
|
|
middlename text,
|
|
affiliation text
|
|
);
|
|
|
|
CREATE TABLE journal (
|
|
id text PRIMARY KEY,
|
|
issn text UNIQUE,
|
|
shorttitle text,
|
|
title text
|
|
);
|
|
|
|
CREATE TABLE conference (
|
|
id text PRIMARY KEY,
|
|
seriestitle text,
|
|
description text,
|
|
seriesid text,
|
|
conferencenumber text,
|
|
acronym text,
|
|
acronymbase text
|
|
);
|
|
|
|
CREATE TABLE citation (
|
|
id text PRIMARY KEY,
|
|
reference text
|
|
);
|
|
|
|
CREATE TABLE organization (
|
|
id text PRIMARY KEY,
|
|
originalid text,
|
|
name text,
|
|
shortname text,
|
|
country text,
|
|
street text,
|
|
city text,
|
|
postcode text,
|
|
url text,
|
|
ec_legalbody boolean,
|
|
ec_legalperson boolean,
|
|
ec_nonprofit boolean,
|
|
ec_researchorganization boolean,
|
|
ec_highereducation boolean,
|
|
ec_internationalorganizationeurinterests boolean,
|
|
ec_internationalorganization boolean,
|
|
ec_enterprise boolean,
|
|
ec_smevalidated boolean,
|
|
ec_nutscode boolean
|
|
);
|
|
|
|
CREATE TABLE topic (
|
|
id text PRIMARY KEY,
|
|
title text,
|
|
category text
|
|
);
|
|
|
|
-- RELATIONS BETWEEN ENTITIES --
|
|
|
|
CREATE TABLE docauthor (
|
|
docid text NOT NULL REFERENCES document(id),
|
|
authorid text NOT NULL REFERENCES author(id),
|
|
profileid text,
|
|
PRIMARY KEY (docid, authorid)
|
|
);
|
|
|
|
CREATE TABLE docproject (
|
|
docid text NOT NULL REFERENCES document(id),
|
|
projectid text NOT NULL REFERENCES project(id),
|
|
PRIMARY KEY (docid, projectid)
|
|
);
|
|
|
|
CREATE TABLE docjournal (
|
|
docid text NOT NULL REFERENCES document(id),
|
|
issn text NOT NULL REFERENCES journal(issn),
|
|
PRIMARY KEY (docid, issn)
|
|
);
|
|
|
|
CREATE TABLE docconference (
|
|
docid text NOT NULL REFERENCES document(id),
|
|
conferenceid text NOT NULL REFERENCES conference(id),
|
|
acronymbase text,
|
|
PRIMARY KEY (docid, conferenceid)
|
|
);
|
|
|
|
CREATE TABLE doccitation (
|
|
docid text NOT NULL REFERENCES document(id),
|
|
citationid text NOT NULL REFERENCES citation(id),
|
|
PRIMARY KEY (docid, citationid)
|
|
);
|
|
|
|
CREATE TABLE projectorganization (
|
|
projectid text NOT NULL REFERENCES project(id),
|
|
orgid text NOT NULL REFERENCES organization(id),
|
|
role text,
|
|
activitytype text,
|
|
endofparticipation text,
|
|
eccontribution text,
|
|
contacttype text,
|
|
contacttitle text,
|
|
contactfirstnames text,
|
|
contactlastnames text,
|
|
contactfunction text,
|
|
contacttelephonenumber text,
|
|
contactfaxnumber text,
|
|
contactform text,
|
|
PRIMARY KEY (projectid,orgid)
|
|
);
|
|
|
|
|
|
CREATE TABLE doctopic (
|
|
docid text NOT NULL REFERENCES document(id),
|
|
topicid text NOT NULL REFERENCES topic(id),
|
|
PRIMARY KEY (docid, topicid)
|
|
);
|
|
|
|
|
|
-- ADDITIONAL TABLES --
|
|
|
|
CREATE TABLE docfulltext (
|
|
docid text PRIMARY KEY REFERENCES document(id),
|
|
fulltext text NOT NULL
|
|
);
|
|
|
|
CREATE TABLE dockeyword (
|
|
docid text NOT NULL REFERENCES document(id),
|
|
keyword text NOT NULL,
|
|
PRIMARY KEY (docid, keyword)
|
|
);
|
|
|
|
CREATE TABLE docpdbcode (
|
|
docid text NOT NULL REFERENCES document(id),
|
|
pdbcode text NOT NULL,
|
|
PRIMARY KEY (docid, pdbcode)
|
|
);
|
|
|
|
CREATE TABLE companymetrics (
|
|
orgid text PRIMARY KEY REFERENCES organization(id),
|
|
data_gathered boolean,
|
|
tangible_pre_market int,
|
|
tangible_market int,
|
|
intangible_pre_market int,
|
|
intangible_market int,
|
|
innovation boolean
|
|
)
|
|
|