Project

General

Profile

« Previous | Next » 

Revision 57097

Added by Michele Artini over 4 years ago

View differences:

schema.sql
9 9 
DROP TABLE IF EXISTS urls;
10 10 
DROP TABLE IF EXISTS openaire_simrels;
11 11 
DROP TABLE IF EXISTS organizations;
12 
DROP TABLE IF EXISTS org_types;
13 
DROP TABLE IF EXISTS id_types;
14 
DROP TABLE IF EXISTS rel_types;
15 
DROP TABLE IF EXISTS simrel_types;
12 16 

  		




  13
  
  
    
DROP TYPE IF EXISTS org_type;


  		




  14
  
  
    
DROP TYPE IF EXISTS id_type;


  		




  15
  
  
    
DROP TYPE IF EXISTS rel_type;


  		




  16
  
  
    
DROP TYPE IF EXISTS simrel_type;


  		




  
  17
  
    
CREATE TABLE org_types (val text PRIMARY KEY);


  		




  
  18
  
    
INSERT INTO org_types VALUES ('Archive'), ('Company'), ('Education'), ('Facility'), ('Government'), ('Healthcare'), ('Nonprofit'), ('Other');


  		




  17
  19
  
    

  		




  18
  
  
    
CREATE TYPE org_type AS ENUM ('Archive', 'Company', 'Education', 'Facility', 'Government', 'Healthcare', 'Nonprofit', 'Other');


  		




  19
  
  
    
CREATE TYPE id_type  AS ENUM ('CNRS', 'FundRef', 'HESA', 'ISNI', 'OrgRef', 'UCAS', 'UKPRN', 'Wikidata', 'grid.ac');


  		




  20
  
  
    
CREATE TYPE rel_type AS ENUM ('Child', 'Parent', 'Related', 'Other');


  		




  21
  
  
    
CREATE TYPE simrel_type AS ENUM ('suggested', 'is_similar', 'is_different');


  		




  
  20
  
    
CREATE TABLE id_types (val text PRIMARY KEY);


  		




  
  21
  
    
INSERT INTO id_types VALUES ('CNRS'), ('FundRef'), ('HESA'), ('ISNI'), ('OrgRef'), ('UCAS'), ('UKPRN'), ('Wikidata'), ('grid.ac');


  		




  22
  22
  
    

  		




  
  23
  
    
CREATE TABLE rel_types (val text PRIMARY KEY);


  		




  
  24
  
    
INSERT INTO rel_types VALUES ('Child'), ('Parent'), ('Related'), ('Other');


  		




  23
  25
  
    

  		




  
  26
  
    
CREATE TABLE simrel_types (val text PRIMARY KEY);


  		




  
  27
  
    
INSERT INTO simrel_types VALUES ('suggested'), ('is_similar'), ('is_different');


  		




  
  28
  
    

  		




  
  29
  
    

  		




  24
  30
  
    
CREATE TABLE organizations (


  		




  25
  31
  
    
    id                text PRIMARY KEY,


  		




  26
  32
  
    
    name              text,


  		




  ......




  37
  43
  
    
CREATE TABLE other_ids (


  		




  38
  44
  
    
	id      text REFERENCES organizations(id),


  		




  39
  45
  
    
	otherid text,


  		




  40
  
  
    
	type    id_type,


  		




  
  46
  
    
	type    text REFERENCES id_types(val),


  		




  41
  47
  
    
	PRIMARY KEY (id, otherid, type)


  		




  42
  48
  
    
);


  		




  43
  49
  
    

  		




  ......




  56
  62
  
    

  		




  57
  63
  
    
CREATE TABLE relationships (


  		




  58
  64
  
    
	id1     text REFERENCES organizations(id),


  		




  59
  
  
    
	reltype rel_type,


  		




  
  65
  
    
	reltype text REFERENCES rel_types(val),


  		




  60
  66
  
    
	id2     text REFERENCES organizations(id),


  		




  61
  67
  
    
    PRIMARY KEY (id1, reltype, id2)


  		




  62
  68
  
    
);


  		




  63
  69
  
    

  		




  64
  70
  
    
CREATE TABLE types (


  		




  65
  71
  
    
	id   text REFERENCES organizations(id),


  		




  66
  
  
    
	type org_type,


  		




  
  72
  
    
	type text REFERENCES org_types(val),


  		




  67
  73
  
    
	PRIMARY KEY (id, type)


  		




  68
  74
  
    
);


  		




  69
  75
  
    

  		




  ......




  82
  88
  
    
	oa_country       text,


  		




  83
  89
  
    
	oa_url           text,


  		




  84
  90
  
    
	oa_collectedfrom text,


  		




  85
  
  
    
	reltype          simrel_type NOT NULL DEFAULT 'suggested',


  		




  
  91
  
    
	reltype          text  NOT NULL DEFAULT 'suggested' REFERENCES simrel_types(val),


  		




  86
  92
  
    
	PRIMARY KEY (local_id, oa_id)


  		




  87
  93
  
    
);


  		




  88
  94
  
    

  		












Also available in:  Unified diff