Project

General

Profile

« Previous | Next » 

Revision 56332

Added by Michele Artini over 5 years ago

View differences:

modules/dnet-orgs-database-application/trunk/src/main/resources/sql/import_grid_ac.sql
81 81 
COPY grid_institutes    (grid_id,name,wikipedia_url,email_address,established) FROM '/Users/michele/Develop/dnet45/dnet-orgs-database-application/data/grid-2019-05-06/full_tables/institutes.csv' CSV HEADER;
82 82 
COPY grid_geonames      (geonames_city_id,city,nuts_level1_code,nuts_level1_name,nuts_level2_code,nuts_level2_name,nuts_level3_code,nuts_level3_name,geonames_admin1_code,geonames_admin1_name,geonames_admin1_ascii_name,geonames_admin2_code,geonames_admin2_name,geonames_admin2_ascii_name) FROM '/Users/michele/Develop/dnet45/dnet-orgs-database-application/data/grid-2019-05-06/full_tables/geonames.csv' CSV HEADER;
83 83 
COPY grid_addresses     (grid_id,line_1,line_2,line_3,lat,lng,postcode,is_primary,city,state,state_code,country,country_code,geonames_city_id) FROM '/Users/michele/Develop/dnet45/dnet-orgs-database-application/data/grid-2019-05-06/full_tables/addresses.csv' CSV HEADER;
84 
COPY grid_external_ids  (grid_id,external_id_type,external_id) FROM '/Users/michele/Develop/dnet45/dnet-orgs-database-application/data/grid-2019-05-06/full_tables/external_ids.csv' CSV HEADER;
85 
COPY grid_labels        (grid_id,iso639,label) FROM '/Users/michele/Develop/dnet45/dnet-orgs-database-application/data/grid-2019-05-06/full_tables/labels.csv' CSV HEADER;
84 
COPY grid_external_ids  (grid_id,external_id_type,external_id)      FROM '/Users/michele/Develop/dnet45/dnet-orgs-database-application/data/grid-2019-05-06/full_tables/external_ids.csv' CSV HEADER;
85 
COPY grid_labels        (grid_id,iso639,label)                      FROM '/Users/michele/Develop/dnet45/dnet-orgs-database-application/data/grid-2019-05-06/full_tables/labels.csv' CSV HEADER;
86 86 
COPY grid_relationships (grid_id,relationship_type,related_grid_id) FROM '/Users/michele/Develop/dnet45/dnet-orgs-database-application/data/grid-2019-05-06/full_tables/relationships.csv' CSV HEADER;
87 
COPY grid_types         (grid_id,type) FROM '/Users/michele/Develop/dnet45/dnet-orgs-database-application/data/grid-2019-05-06/full_tables/types.csv' CSV HEADER;
88 
COPY grid_links         (grid_id,link) FROM '/Users/michele/Develop/dnet45/dnet-orgs-database-application/data/grid-2019-05-06/full_tables/links.csv' CSV HEADER;
89 
COPY grid_acronyms (grid_id,acronym) FROM '/Users/michele/Develop/dnet45/dnet-orgs-database-application/data/grid-2019-05-06/full_tables/acronyms.csv' CSV HEADER;
90 
COPY grid_aliases  (grid_id,alias)   FROM '/Users/michele/Develop/dnet45/dnet-orgs-database-application/data/grid-2019-05-06/full_tables/aliases.csv'  CSV HEADER;
87 
COPY grid_types         (grid_id,type)                              FROM '/Users/michele/Develop/dnet45/dnet-orgs-database-application/data/grid-2019-05-06/full_tables/types.csv' CSV HEADER;
88 
COPY grid_links         (grid_id,link)                              FROM '/Users/michele/Develop/dnet45/dnet-orgs-database-application/data/grid-2019-05-06/full_tables/links.csv' CSV HEADER;
89 
COPY grid_acronyms      (grid_id,acronym)                           FROM '/Users/michele/Develop/dnet45/dnet-orgs-database-application/data/grid-2019-05-06/full_tables/acronyms.csv' CSV HEADER;
90 
COPY grid_aliases       (grid_id,alias)                             FROM '/Users/michele/Develop/dnet45/dnet-orgs-database-application/data/grid-2019-05-06/full_tables/aliases.csv'  CSV HEADER;
91 91 

  		




  92
  92
  
    

  		




  93
  93
  
    
INSERT INTO organizations(id, name, lat, lng, city, country, source) (SELECT 'openaire____::'||md5(o.grid_id), o.name, a.lat, a.lng, a.city, a.country_code, 'grid.ac' FROM grid_institutes o LEFT OUTER JOIN grid_addresses a ON (o.grid_id=a.grid_id)) ON CONFLICT DO NOTHING;


  		




  94
  94
  
    
INSERT INTO other_ids    (id, otherid, type)                         (SELECT 'openaire____::'||md5(grid_id), grid_id,     'grid.ac'                                    FROM grid_institutes   ) ON CONFLICT DO NOTHING;


  		




  95
  95
  
    
INSERT INTO other_ids    (id, otherid, type)                         (SELECT 'openaire____::'||md5(grid_id), external_id, external_id_type                             FROM grid_external_ids ) ON CONFLICT DO NOTHING;


  		




  96
  
  
    
INSERT INTO labels       (id, lang, label)                           (SELECT 'openaire____::'||md5(grid_id), iso639, label                                             FROM grid_labels       ) ON CONFLICT DO NOTHING;


  		




  
  96
  
    
INSERT INTO other_names  (id, lang, name)                            (SELECT 'openaire____::'||md5(grid_id), 'en',   name                                              FROM grid_institutes   ) ON CONFLICT DO NOTHING;


  		




  
  97
  
    
INSERT INTO other_names  (id, lang, name)                            (SELECT 'openaire____::'||md5(grid_id), iso639, label                                             FROM grid_labels       ) ON CONFLICT DO NOTHING;


  		




  
  98
  
    
INSERT INTO other_names  (id, lang, name)                            (SELECT 'openaire____::'||md5(grid_id), '',     alias                                             FROM grid_aliases      ) ON CONFLICT DO NOTHING;


  		




  
  99
  
    
INSERT INTO acronyms     (id, acronym)                               (SELECT 'openaire____::'||md5(grid_id), acronym                                                   FROM grid_acronyms     ) ON CONFLICT DO NOTHING;


  		




  97
  100
  
    
INSERT INTO relationships(id1, reltype, id2)                         (SELECT 'openaire____::'||md5(grid_id), relationship_type, 'openaire____::'||md5(related_grid_id) FROM grid_relationships) ON CONFLICT DO NOTHING;


  		




  98
  101
  
    
INSERT INTO types        (id, type)                                  (SELECT 'openaire____::'||md5(grid_id), type                                                      FROM grid_types        ) ON CONFLICT DO NOTHING;


  		




  99
  102
  
    
INSERT INTO urls         (id, url)                                   (SELECT 'openaire____::'||md5(grid_id), link                                                      FROM grid_links        ) ON CONFLICT DO NOTHING;


  		




  100
  
  
    
INSERT INTO acronyms     (id, acronym)                               (SELECT 'openaire____::'||md5(grid_id), acronym                                                   FROM grid_acronyms     ) ON CONFLICT DO NOTHING;


  		




  101
  
  
    
INSERT INTO aliases      (id, alias)                                 (SELECT 'openaire____::'||md5(grid_id), alias                                                     FROM grid_aliases      ) ON CONFLICT DO NOTHING;


  		




  102
  103
  
    

  		




  103
  
  
    

  		




  104
  
  
    

  		




  105
  
  
    

  		




  106
  
  
    

  		




  107
  
  
    

  		




  108
  
  
    

  		












  

    
      modules/dnet-orgs-database-application/trunk/src/main/resources/sql/schema.sql
    
  





  1
  1
  
    
DROP VIEW organizations_view;


  		




  2
  2
  
    

  		




  3
  3
  
    
DROP TABLE IF EXISTS other_ids;


  		




  4
  
  
    
DROP TABLE IF EXISTS labels;


  		




  
  4
  
    
DROP TABLE IF EXISTS other_names;


  		




  
  5
  
    
DROP TABLE IF EXISTS acronyms;


  		




  5
  6
  
    
DROP TABLE IF EXISTS relationships;


  		




  6
  7
  
    
DROP TABLE IF EXISTS types;


  		




  7
  8
  
    
DROP TABLE IF EXISTS urls;


  		




  8
  
  
    
DROP TABLE IF EXISTS acronyms;


  		




  9
  
  
    
DROP TABLE IF EXISTS aliases;


  		




  10
  9
  
    
DROP TABLE IF EXISTS organizations;


  		




  11
  10
  
    

  		




  12
  11
  
    
CREATE TABLE organizations (


  		




  ......




  26
  25
  
    
	PRIMARY KEY (id, otherid, type)


  		




  27
  26
  
    
);


  		




  28
  27
  
    

  		




  29
  
  
    
CREATE TABLE labels (


  		




  
  28
  
    
CREATE TABLE other_names (


  		




  30
  29
  
    
	id    text REFERENCES organizations(id),


  		




  31
  
  
    
	lang  text,


  		




  32
  
  
    
	label text,


  		




  33
  
  
    
	PRIMARY KEY (id, lang, label)


  		




  
  30
  
    
	name  text,


  		




  
  31
  
    
	lang  text DEFAULT '',


  		




  
  32
  
    
	PRIMARY KEY (id, name, lang)


  		




  34
  33
  
    
);


  		




  35
  34
  
    

  		




  
  35
  
    
CREATE TABLE acronyms (


  		




  
  36
  
    
	id text,


  		




  
  37
  
    
	acronym text,


  		




  
  38
  
    
	PRIMARY KEY (id, acronym)


  		




  
  39
  
    
);


  		




  
  40
  
    

  		




  36
  41
  
    
CREATE TABLE relationships (


  		




  37
  42
  
    
	id1     text REFERENCES organizations(id),


  		




  38
  43
  
    
	reltype text,


  		




  ......




  52
  57
  
    
	PRIMARY KEY (id, url)


  		




  53
  58
  
    
);


  		




  54
  59
  
    

  		




  55
  
  
    
CREATE TABLE acronyms (


  		




  56
  
  
    
	id      text REFERENCES organizations(id),


  		




  57
  
  
    
	acronym text,


  		




  58
  
  
    
	PRIMARY KEY (id, acronym)


  		




  59
  
  
    
);


  		




  60
  
  
    

  		




  61
  
  
    
CREATE TABLE aliases (


  		




  62
  
  
    
	id      text REFERENCES organizations(id),


  		




  63
  
  
    
	alias   text,


  		




  64
  
  
    
	PRIMARY KEY (id, alias)


  		




  65
  
  
    
);


  		




  66
  
  
    

  		




  67
  60
  
    
CREATE VIEW organizations_view AS SELECT


  		




  68
  61
  
    
    org.id,


  		




  69
  62
  
    
    org.name,


  		




  ......




  72
  65
  
    
	org.city,


  		




  73
  66
  
    
	org.country,


  		




  74
  67
  
    
	org.source,


  		




  75
  
  
    
	COALESCE(json_agg(json_build_object('id',oid.otherid,'type',oid.type))    FILTER (WHERE oid.otherid IS NOT NULL), '[]') AS other_ids,--TODO AGGIUNGERE DISTINCT


  		




  76
  
  
    
	COALESCE(json_agg(json_build_object('label',l.label,'lang',l.lang))       FILTER (WHERE l.label     IS NOT NULL), '[]') AS labels,--TODO AGGIUNGERE DISTINCT


  		




  77
  
  
    
	COALESCE(json_agg(json_build_object('related_to',r.id2,'type',r.reltype)) FILTER (WHERE r.id2       IS NOT NULL), '[]') AS relations,--TODO AGGIUNGERE DISTINCT


  		




  78
  
  
    
	array_to_json(array_remove(array_agg(DISTINCT t.type    ), NULL)) as types,


  		




  79
  
  
    
	array_to_json(array_remove(array_agg(DISTINCT u.url     ), NULL)) as urls,


  		




  80
  
  
    
	array_to_json(array_remove(array_agg(DISTINCT ac.acronym), NULL)) as acronyms,


  		




  81
  
  
    
    array_to_json(array_remove(array_agg(DISTINCT al.alias  ), NULL)) as aliases


  		




  
  68
  
    
	COALESCE(jsonb_agg(DISTINCT jsonb_build_object('id', oid.otherid, 'type', oid.type))                                    FILTER (WHERE oid.otherid IS NOT NULL), '[]') AS other_ids,


  		




  
  69
  
    
	COALESCE(jsonb_agg(DISTINCT jsonb_build_object('name',          n.name, 'lang', n.lang))                                FILTER (WHERE n.name      IS NOT NULL), '[]') AS other_names,


  		




  
  70
  
    
	COALESCE(jsonb_agg(DISTINCT jsonb_build_object('related_to_id', r.id2,  'type', r.reltype, 'related_to_name', ro.name)) FILTER (WHERE r.id2       IS NOT NULL), '[]') AS relations,


  		




  
  71
  
    
	COALESCE(jsonb_agg(DISTINCT a.acronym)                                                                                  FILTER (WHERE a.acronym   IS NOT NULL), '[]') AS acronyms,	


  		




  
  72
  
    
	COALESCE(jsonb_agg(DISTINCT t.type)                                                                                     FILTER (WHERE t.type      IS NOT NULL), '[]') AS types,


  		




  
  73
  
    
	COALESCE(jsonb_agg(DISTINCT u.url)                                                                                      FILTER (WHERE u.url       IS NOT NULL), '[]') AS urls


  		




  82
  74
  
    
FROM


  		




  83
  75
  
    
    organizations org


  		




  84
  
  
    
    LEFT OUTER JOIN other_ids oid   ON (org.id = oid.id)


  		




  85
  
  
    
    LEFT OUTER JOIN labels l        ON (org.id = l.id)


  		




  86
  
  
    
    LEFT OUTER JOIN relationships r ON (org.id = r.id1)


  		




  87
  
  
    
    LEFT OUTER JOIN types t         ON (org.id = t.id)


  		




  88
  
  
    
    LEFT OUTER JOIN urls u          ON (org.id = u.id)


  		




  89
  
  
    
    LEFT OUTER JOIN acronyms ac     ON (org.id = ac.id)


  		




  90
  
  
    
    LEFT OUTER JOIN aliases al      ON (org.id = al.id)


  		




  
  76
  
    
    LEFT OUTER JOIN other_ids oid    ON (org.id = oid.id)


  		




  
  77
  
    
    LEFT OUTER JOIN other_names n    ON (org.id = n.id)


  		




  
  78
  
    
    LEFT OUTER JOIN relationships r  ON (org.id = r.id1)


  		




  
  79
  
    
    LEFT OUTER JOIN organizations ro ON (ro.id  = r.id2)


  		




  
  80
  
    
    LEFT OUTER JOIN acronyms a       ON (org.id = a.id)


  		




  
  81
  
    
    LEFT OUTER JOIN types t          ON (org.id = t.id)


  		




  
  82
  
    
    LEFT OUTER JOIN urls u           ON (org.id = u.id)


  		




  91
  83
  
    
GROUP BY


  		




  92
  84
  
    
    org.id,


  		




  93
  85
  
    
    org.name,


  		




  ......




  97
  89
  
    
	org.country,


  		




  98
  90
  
    
	org.source;


  		




  99
  91
  
    
	


  		




  100
  
  
    
	


  		




  101
  
  
    
	


  		




  102
  
  
    
	


  		




  103
  
  
    
	


  		




  104
  
  
    
	


  		




  105
  
  
    
	


  		




  106
  
  
    
	


  		




  107
  
  
    
	


  		




  108
  
  
    
	


  		




  109
  
  
    
	


  		




  110
  
  
    
	


  		




  111
  
  
    
	


  		




  112
  
  
    
	


  		




  113
  
  
    
	


  		




  114
  
  
    

  		




  115
  
  
    

  		




  116
  92
  
    
	


  		












Also available in:  Unified diff