Project

General

Profile

« Previous | Next » 

Revision 57304

Added by Michele Artini almost 5 years ago

sequential identifiers

View differences:

modules/dnet-orgs-database-application/trunk/src/main/java/eu/dnetlib/organizations/model/Organization.java
4 4 

  		




  5
  5
  
    
import javax.persistence.Column;


  		




  6
  6
  
    
import javax.persistence.Entity;


  		




  
  7
  
    
import javax.persistence.GeneratedValue;


  		




  
  8
  
    
import javax.persistence.GenerationType;


  		




  7
  9
  
    
import javax.persistence.Id;


  		




  8
  10
  
    
import javax.persistence.Table;


  		




  9
  11
  
    

  		




  ......




  18
  20
  
    

  		




  19
  21
  
    
	@Id


  		




  20
  22
  
    
	@Column(name = "id")


  		




  
  23
  
    
	@GeneratedValue(strategy = GenerationType.IDENTITY)


  		




  21
  24
  
    
	private String id;


  		




  22
  25
  
    

  		




  23
  26
  
    
	@Column(name = "name")


  		












  

    
      modules/dnet-orgs-database-application/trunk/src/main/java/eu/dnetlib/organizations/utils/DatabaseUtils.java
    
  





  2
  2
  
    

  		




  3
  3
  
    
import java.time.OffsetDateTime;


  		




  4
  4
  
    
import java.util.List;


  		




  5
  
  
    
import java.util.UUID;


  		




  6
  5
  
    

  		




  7
  6
  
    
import javax.transaction.Transactional;


  		




  8
  7
  
    

  		




  9
  
  
    
import org.apache.commons.codec.digest.DigestUtils;


  		




  
  8
  
    
import org.apache.commons.lang3.StringUtils;


  		




  10
  9
  
    
import org.springframework.beans.factory.annotation.Autowired;


  		




  11
  10
  
    
import org.springframework.cache.annotation.Cacheable;


  		




  12
  11
  
    
import org.springframework.jdbc.core.JdbcTemplate;


  		




  ......




  50
  49
  
    
	}


  		




  51
  50
  
    

  		




  52
  51
  
    
	@Transactional


  		




  53
  
  
    
	public String insertOrUpdateOrganization(final OrganizationView org, final String user, final boolean update) {


  		




  
  52
  
    
	public String insertOrUpdateOrganization(final OrganizationView orgView, final String user, final boolean update) {


  		




  54
  53
  
    

  		




  55
  
  
    
		final String orgId = update ? org.getId() : "openorgs____::" + DigestUtils.md5Hex(UUID.randomUUID().toString());


  		




  56
  
  
    
		final OffsetDateTime now = OffsetDateTime.now();


  		




  57
  
  
    

  		




  58
  54
  
    
		if (update) {


  		




  59
  
  
    
			acronymRepository.deleteByOrgId(orgId);


  		




  60
  
  
    
			otherNameRepository.deleteByOrgId(orgId);


  		




  61
  
  
    
			otherIdentifierRepository.deleteByOrgId(orgId);


  		




  62
  
  
    
			urlRepository.deleteByOrgId(orgId);


  		




  
  55
  
    
			cleanOldRelations(orgView.getId());


  		




  63
  56
  
    
		}


  		




  64
  57
  
    

  		




  65
  
  
    
		organizationRepository.save(new Organization(orgId, org.getName(), org.getType(), org.getLat(), org.getLng(), org.getCity(), org.getCountry()));


  		




  66
  
  
    
		org.getAcronyms().forEach(s -> acronymRepository.save(new Acronym(orgId, s)));


  		




  67
  
  
    
		org.getOtherNames().forEach(n -> otherNameRepository.save(new OtherName(orgId, n.getName(), n.getLang())));


  		




  68
  
  
    
		org.getOtherIdentifiers().forEach(id -> otherIdentifierRepository.save(new OtherIdentifier(orgId, id.getId(), id.getType())));


  		




  69
  
  
    
		org.getUrls().forEach(u -> urlRepository.save(new Url(orgId, u)));


  		




  
  58
  
    
		final Organization org = new Organization(update ? orgView.getId() : null,


  		




  
  59
  
    
				orgView.getName(),


  		




  
  60
  
    
				StringUtils.isNotBlank(orgView.getType()) ? orgView.getType() : "UNKNOWN",


  		




  
  61
  
    
				orgView.getLat(), orgView.getLng(),


  		




  
  62
  
    
				orgView.getCity(), orgView.getCountry());


  		




  70
  63
  
    

  		




  
  64
  
    
		final String orgId = organizationRepository.save(org).getId();


  		




  
  65
  
    

  		




  
  66
  
    
		makeNewRelations(orgView, orgId);


  		




  
  67
  
    

  		




  
  68
  
    
		updateHistoryFields(orgId, user, update);


  		




  
  69
  
    

  		




  
  70
  
    
		return orgId;


  		




  
  71
  
    
	}


  		




  
  72
  
    

  		




  
  73
  
    
	private void updateHistoryFields(final String id, final String user, final boolean update) {


  		




  
  74
  
    
		final OffsetDateTime now = OffsetDateTime.now();


  		




  71
  75
  
    
		if (update) {


  		




  72
  
  
    
			organizationRepository.updateModificationDate(orgId, user, now);


  		




  
  76
  
    
			organizationRepository.updateModificationDate(id, user, now);


  		




  73
  77
  
    
		} else {


  		




  74
  
  
    
			organizationRepository.updateCreationDate(orgId, user, now);


  		




  75
  
  
    
			organizationRepository.updateModificationDate(orgId, user, now);


  		




  
  78
  
    
			organizationRepository.updateCreationDate(id, user, now);


  		




  
  79
  
    
			organizationRepository.updateModificationDate(id, user, now);


  		




  76
  80
  
    
		}


  		




  
  81
  
    
	}


  		




  77
  82
  
    

  		




  78
  
  
    
		return orgId;


  		




  
  83
  
    
	private void makeNewRelations(final OrganizationView orgView, final String orgId) {


  		




  
  84
  
    
		orgView.getAcronyms().forEach(s -> acronymRepository.save(new Acronym(orgId, s)));


  		




  
  85
  
    
		orgView.getOtherNames().forEach(n -> otherNameRepository.save(new OtherName(orgId, n.getName(), n.getLang())));


  		




  
  86
  
    
		orgView.getOtherIdentifiers().forEach(id -> otherIdentifierRepository.save(new OtherIdentifier(orgId, id.getId(), id.getType())));


  		




  
  87
  
    
		orgView.getUrls().forEach(u -> urlRepository.save(new Url(orgId, u)));


  		




  79
  88
  
    
	}


  		




  80
  89
  
    

  		




  
  90
  
    
	private void cleanOldRelations(final String id) {


  		




  
  91
  
    
		acronymRepository.deleteByOrgId(id);


  		




  
  92
  
    
		otherNameRepository.deleteByOrgId(id);


  		




  
  93
  
    
		otherIdentifierRepository.deleteByOrgId(id);


  		




  
  94
  
    
		urlRepository.deleteByOrgId(id);


  		




  
  95
  
    
	}


  		




  
  96
  
    

  		




  81
  97
  
    
	@Cacheable("vocs")


  		




  82
  98
  
    
	public List<String> listValuesOfVocabularyTable(final VocabularyTable table) {


  		




  83
  99
  
    
		return jdbcTemplate.queryForList("select val from " + table, String.class);


  		












  

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





  90
  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
  
  
    
INSERT INTO organizations(id, name, type, lat, lng, city, country, created_by, modified_by) (SELECT 'openorgs____::'||md5(o.grid_id), o.name, COALESCE(t.type, 'Other'), a.lat, a.lng, a.city, a.country_code, 'import:grid.ac', 'import:grid.ac'  FROM grid_institutes o LEFT OUTER JOIN grid_addresses a ON (o.grid_id=a.grid_id) LEFT OUTER JOIN grid_types t ON (o.grid_id=t.grid_id)) ON CONFLICT DO NOTHING;


  		




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


  		




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


  		




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


  		




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


  		




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


  		




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


  		




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


  		




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


  		




  
  93
  
    
INSERT INTO organizations(id, name, type, lat, lng, city, country, created_by, modified_by) (SELECT 'tmp::'||md5(o.grid_id), o.name, COALESCE(t.type, 'Other'), a.lat, a.lng, a.city, a.country_code, 'import:grid.ac', 'import:grid.ac'  FROM grid_institutes o LEFT OUTER JOIN grid_addresses a ON (o.grid_id=a.grid_id) LEFT OUTER JOIN grid_types t ON (o.grid_id=t.grid_id)) ON CONFLICT DO NOTHING;


  		




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


  		




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


  		




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


  		




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


  		




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


  		




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


  		




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


  		




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


  		




  102
  102
  
    

  		




  
  103
  
    
update organizations set id = DEFAULT;


  		












  

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





  1
  
  
    
INSERT INTO openaire_simrels(local_id, oa_id, oa_original_id, oa_name, oa_acronym, oa_country, oa_url, oa_collectedfrom) VALUES ('openorgs____::000248a304b41b83bed813260b2afec1','opendoar____::b504d1345f7d151031fd7b96333704f0','2323','ITT Technical Institute','ITT','UK','http://','OpenDOAR');


  		




  2
  
  
    
INSERT INTO openaire_simrels(local_id, oa_id, oa_original_id, oa_name, oa_acronym, oa_country, oa_url, oa_collectedfrom) VALUES ('openorgs____::000248a304b41b83bed813260b2afec1','ec__________::927fd71a2e566f9f45ce51047a4db54a','986786','Technical Institute','ITT-TI','UK','http://','EC');


  		




  3
  
  
    
INSERT INTO openaire_simrels(local_id, oa_id, oa_original_id, oa_name, oa_acronym, oa_country, oa_url, oa_collectedfrom) VALUES ('openorgs____::000248a304b41b83bed813260b2afec1','re3data_____::84913f34770f5f7f92af45775009580d','r3344','Institute of Informatics and Telematics','IIT','IT','http://','re3data');


  		




  4
  
  
    
INSERT INTO openaire_simrels(local_id, oa_id, oa_original_id, oa_name, oa_acronym, oa_country, oa_url, oa_collectedfrom) VALUES ('openorgs____::000248a304b41b83bed813260b2afec1','ec__________::9fdd5957f12affa9b7e50cad05c280b3', '9994324','Istituto Italiano di Tecnologia ','IIT','IT','http://','EC');


  		




  
  1
  
    
INSERT INTO openaire_simrels(local_id, oa_id, oa_original_id, oa_name, oa_acronym, oa_country, oa_url, oa_collectedfrom) VALUES ('openorgs____::0000022403','opendoar____::b504d1345f7d151031fd7b96333704f0','2323','ITT Technical Institute','ITT','UK','http://','OpenDOAR');


  		




  
  2
  
    
INSERT INTO openaire_simrels(local_id, oa_id, oa_original_id, oa_name, oa_acronym, oa_country, oa_url, oa_collectedfrom) VALUES ('openorgs____::0000022403','ec__________::927fd71a2e566f9f45ce51047a4db54a','986786','Technical Institute','ITT-TI','UK','http://','EC');


  		




  
  3
  
    
INSERT INTO openaire_simrels(local_id, oa_id, oa_original_id, oa_name, oa_acronym, oa_country, oa_url, oa_collectedfrom) VALUES ('openorgs____::0000022403','re3data_____::84913f34770f5f7f92af45775009580d','r3344','Institute of Informatics and Telematics','IIT','IT','http://','re3data');


  		




  
  4
  
    
INSERT INTO openaire_simrels(local_id, oa_id, oa_original_id, oa_name, oa_acronym, oa_country, oa_url, oa_collectedfrom) VALUES ('openorgs____::0000022403','ec__________::9fdd5957f12affa9b7e50cad05c280b3', '9994324','Istituto Italiano di Tecnologia ','IIT','IT','http://','EC');


  		




  5
  5
  
    

  		




  6
  6
  
    
INSERT INTO users(email, password, valid, role) VALUES ('michele', '{MD5}'||MD5('dnet'), true, 'SUPERUSER');


  		




  7
  7
  
    
INSERT INTO users(email, password, valid, role) VALUES ('emma',   '{MD5}'||MD5('dnet'), true, 'USER');


  		












  

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





  18
  18
  
    
DROP TABLE IF EXISTS id_types;


  		




  19
  19
  
    
DROP TABLE IF EXISTS languages;


  		




  20
  20
  
    

  		




  
  21
  
    
DROP SEQUENCE IF EXISTS organizations_id_seq;


  		




  21
  22
  
    

  		




  22
  
  
    

  		




  23
  23
  
    
CREATE TABLE org_types (val text PRIMARY KEY);


  		




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


  		




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


  		




  25
  25
  
    

  		




  26
  26
  
    
CREATE TABLE id_types (val text PRIMARY KEY);


  		




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


  		




  ......




  51
  51
  
    
CREATE TABLE users (


  		




  52
  52
  
    
	email     text PRIMARY KEY,


  		




  53
  53
  
    
	password  text NOT NULL,


  		




  54
  
  
    
	valid     boolean default true,


  		




  
  54
  
    
	valid     boolean DEFAULT true,


  		




  55
  55
  
    
	role      text NOT NULL default 'USER' REFERENCES user_roles(role)


  		




  56
  56
  
    
);


  		




  57
  57
  
    

  		




  ......




  61
  61
  
    
	PRIMARY KEY(email, country)	


  		




  62
  62
  
    
);


  		




  63
  63
  
    

  		




  
  64
  
    

  		




  
  65
  
    
CREATE SEQUENCE organizations_id_seq;


  		




  
  66
  
    

  		




  64
  67
  
    
CREATE TABLE organizations (


  		




  65
  
  
    
    id                text PRIMARY KEY,


  		




  
  68
  
    
    id                text PRIMARY KEY DEFAULT 'openorgs____::'||lpad(nextval('organizations_id_seq')::text,10,'0'),


  		




  66
  69
  
    
    name              text,


  		




  67
  
  
    
   	type              text NOT NULL default 'Other' REFERENCES org_types(val),


  		




  
  70
  
    
   	type              text NOT NULL DEFAULT 'UNKNOWN' REFERENCES org_types(val),


  		




  68
  71
  
    
    lat               double precision,


  		




  69
  72
  
    
	lng               double precision,


  		




  70
  73
  
    
	city              text,


  		




  ......




  74
  77
  
    
	modified_by       text,


  		




  75
  78
  
    
	modification_date timestamp with time zone DEFAULT now()


  		




  76
  79
  
    
);


  		




  
  80
  
    
CREATE INDEX organizations_type_idx ON organizations(type);


  		




  
  81
  
    
CREATE INDEX organizations_country_idx ON organizations(country);


  		




  77
  82
  
    

  		




  78
  83
  
    
CREATE TABLE other_ids (


  		




  79
  
  
    
	id      text REFERENCES organizations(id),


  		




  
  84
  
    
	id      text REFERENCES organizations(id) ON UPDATE CASCADE,


  		




  80
  85
  
    
	otherid text,


  		




  81
  86
  
    
	type    text REFERENCES id_types(val),


  		




  82
  87
  
    
	PRIMARY KEY (id, otherid, type)


  		




  83
  88
  
    
);


  		




  
  89
  
    
CREATE INDEX other_ids_id_idx ON other_ids(id);


  		




  84
  90
  
    

  		




  85
  91
  
    
CREATE TABLE other_names (


  		




  86
  
  
    
	id    text REFERENCES organizations(id),


  		




  
  92
  
    
	id    text REFERENCES organizations(id) ON UPDATE CASCADE,


  		




  87
  93
  
    
	name  text,


  		




  88
  94
  
    
	lang  text REFERENCES languages(val),


  		




  89
  95
  
    
	PRIMARY KEY (id, name, lang)


  		




  90
  96
  
    
);


  		




  
  97
  
    
CREATE INDEX other_names_id_idx ON other_names(id);


  		




  91
  98
  
    

  		




  92
  99
  
    
CREATE TABLE acronyms (


  		




  93
  
  
    
	id text,


  		




  
  100
  
    
	id text  REFERENCES organizations(id) ON UPDATE CASCADE,


  		




  94
  101
  
    
	acronym text,


  		




  95
  102
  
    
	PRIMARY KEY (id, acronym)


  		




  96
  103
  
    
);


  		




  
  104
  
    
CREATE INDEX acronyms_id_idx ON acronyms(id);


  		




  97
  105
  
    

  		




  98
  106
  
    
CREATE TABLE relationships (


  		




  99
  
  
    
	id1     text REFERENCES organizations(id),


  		




  
  107
  
    
	id1     text REFERENCES organizations(id) ON UPDATE CASCADE,


  		




  100
  108
  
    
	reltype text,


  		




  101
  
  
    
	id2     text REFERENCES organizations(id),


  		




  
  109
  
    
	id2     text REFERENCES organizations(id) ON UPDATE CASCADE,


  		




  102
  110
  
    
    PRIMARY KEY (id1, reltype, id2)


  		




  103
  111
  
    
);


  		




  
  112
  
    
CREATE INDEX relationships_id1_idx ON relationships(id1);


  		




  
  113
  
    
CREATE INDEX relationships_id2_idx ON relationships(id2);


  		




  104
  114
  
    

  		




  105
  115
  
    
CREATE TABLE urls (


  		




  106
  
  
    
	id  text REFERENCES organizations(id),


  		




  
  116
  
    
	id  text REFERENCES organizations(id) ON UPDATE CASCADE,


  		




  107
  117
  
    
	url text,


  		




  108
  118
  
    
	PRIMARY KEY (id, url)


  		




  109
  119
  
    
);


  		




  
  120
  
    
CREATE INDEX urls_id_idx ON urls(id);


  		




  110
  121
  
    

  		




  111
  122
  
    
CREATE TABLE openaire_simrels (


  		




  112
  
  
    
	local_id         text REFERENCES organizations(id),


  		




  
  123
  
    
	local_id         text REFERENCES organizations(id) ON UPDATE CASCADE,


  		




  113
  124
  
    
	oa_id            text,


  		




  114
  125
  
    
	oa_original_id   text,


  		




  115
  126
  
    
	oa_name          text,


  		




  ......




  120
  131
  
    
	reltype          text  NOT NULL DEFAULT 'suggested',


  		




  121
  132
  
    
	PRIMARY KEY (local_id, oa_id)


  		




  122
  133
  
    
);


  		




  
  134
  
    
CREATE INDEX openaire_simrels_local_id_idx ON openaire_simrels(local_id);


  		




  123
  135
  
    

  		




  124
  136
  
    
CREATE VIEW organizations_view AS SELECT


  		




  125
  137
  
    
    org.id,


  		












  

    
      modules/dnet-orgs-database-application/trunk/src/main/resources/static/resources/js/organizations.js
    
  





  44
  44
  
    
	$scope.org = {


  		




  45
  45
  
    
			"id": "",


  		




  46
  46
  
    
			"name": "",


  		




  
  47
  
    
			"type": null,


  		




  47
  48
  
    
			"lat": 0.0,


  		




  48
  49
  
    
			"lng": 0.0,


  		




  49
  50
  
    
			"city": "",


  		




  ......




  53
  54
  
    
			"otherNames": [],


  		




  54
  55
  
    
			"relations": [],


  		




  55
  56
  
    
			"acronyms": [],


  		




  56
  
  
    
			"types": [],


  		




  57
  57
  
    
			"urls": []


  		




  58
  58
  
    
		};


  		




  59
  59
  
    
	$scope.vocabularies = {};


  		












Also available in:  Unified diff