Project

General

Profile

« Previous | Next » 

Revision 48139

Added by Alessia Bardi almost 7 years ago

integrated (hopefully) all required changes from dnet40

View differences:

pangaea_journal_findmatches.sql
1 
INSERT INTO hostedby_map(_dnet_resource_identifier_, oa_source_id, entry, datasourceid)
2 
SELECT   p.id ||'@@'||p.oa_source_id, p.oa_source_id, p.id , d.id
3 
  FROM  datasources d, pangaea_temp_journals p  where
4 
	d.collectedfrom ='driver______::1790119e-d281-4b7a-aedf-866d1d853a07' and d.issn is not null and d.id like 'doajarticles::%' and p.issn = d.issn
5 
	AND
6 
	(p.oa_source_id, p.id) NOT IN
7 
		(SELECT oa_source_id, entry from hostedby_map);
1 
INSERT INTO hostedby_map (_dnet_resource_identifier_, oa_source_id, entry, datasourceid)
2 
	SELECT
3 
		p.id || '@@' || p.oa_source_id,
4 
		p.oa_source_id,
5 
		p.id,
6 
		d.id
7 
	FROM datasources d, pangaea_temp_journals p
8 
	WHERE
9 
		d.collectedfrom = 'driver______::1790119e-d281-4b7a-aedf-866d1d853a07' AND d.issn IS NOT NULL AND d.id LIKE 'doajarticles::%' AND p.issn = d.issn
10 
		AND
11 
		(p.oa_source_id, p.id) NOT IN
12 
		(SELECT
13 
			 oa_source_id,
14 
			 entry
15 
		 FROM hostedby_map);
8 16 

  		




  9
  17
  
    

  		




  10
  
  
    
INSERT INTO hostedby_props( _dnet_resource_identifier_,key, value)


  		




  11
  
  
    
SELECT p.id ||'@@'||p.oa_source_id, 'issn', p.issn


  		




  12
  
  
    
FROM  datasources d, pangaea_temp_journals p  where 


  		




  13
  
  
    
	d.collectedfrom ='driver______::1790119e-d281-4b7a-aedf-866d1d853a07' and d.issn is not null and d.id like 'doajarticles::%' and p.issn = d.issn


  		




  14
  
  
    
	AND 


  		




  15
  
  
    
	(p.id ||'@@'||p.oa_source_id, 'issn', p.issn) NOT IN


  		




  16
  
  
    
		(SELECT  _dnet_resource_identifier_,key, value from hostedby_props);


  		




  
  18
  
    
INSERT INTO hostedby_props (_dnet_resource_identifier_, key, value)


  		




  
  19
  
    
	SELECT


  		




  
  20
  
    
		p.id || '@@' || p.oa_source_id,


  		




  
  21
  
    
		'issn',


  		




  
  22
  
    
		p.issn


  		




  
  23
  
    
	FROM datasources d, pangaea_temp_journals p


  		




  
  24
  
    
	WHERE


  		




  
  25
  
    
		d.collectedfrom = 'driver______::1790119e-d281-4b7a-aedf-866d1d853a07' AND d.issn IS NOT NULL AND d.id LIKE 'doajarticles::%' AND p.issn = d.issn


  		




  
  26
  
    
		AND


  		




  
  27
  
    
		(p.id || '@@' || p.oa_source_id, 'issn', p.issn) NOT IN


  		




  
  28
  
    
		(SELECT


  		




  
  29
  
    
			 _dnet_resource_identifier_,


  		




  
  30
  
    
			 key,


  		




  
  31
  
    
			 value


  		




  
  32
  
    
		 FROM hostedby_props);


  		












Also available in:  Unified diff