Project

General

Profile

« Previous | Next » 

Revision 46537

OpenAIRE project exporter with Spring boot

View differences:

modules/dnet-openaire-projects-exporter/trunk/deploy.info
1
{"type_source": "SVN", "goal": "package -U source:jar", 
2
"url": "http://svn-public.driver.research-infrastructures.eu/driver/dnet45/modules/dnet-openaire-projects-exporter/trunk/", "deploy_repository": "dnet45-snapshots", "version": "4", "mail": "sandro.labruzzo@isti.cnr.it,michele.artini@isti.cnr.it, claudio.atzori@isti.cnr.it, alessia.bardi@isti.cnr.it", 
3
"deploy_repository_url": "http://maven.research-infrastructures.eu/nexus/content/repositories/dnet45-snapshots", "name": "dnet-openaireplus-mapping-utils"}
modules/dnet-openaire-projects-exporter/trunk/src/main/resources/eu/dnetlib/openaire/exporter/st/projects_dspace_tail.st
1
 </value-pairs>
2
</form-value-pairs>
modules/dnet-openaire-projects-exporter/trunk/dnet-openaire-projects-exporter.iml
1
<?xml version="1.0" encoding="UTF-8"?>
2
<module org.jetbrains.idea.maven.project.MavenProjectsManager.isMavenModule="true" type="JAVA_MODULE" version="4">
3
  <component name="FacetManager">
4
    <facet type="Spring" name="Spring">
5
      <configuration>
6
        <fileset id="fileset" name="Spring Application Context" removed="false">
7
          <file>file://$MODULE_DIR$/src/main/resources/eu/dnetlib/openaire/exporter/webContext-openaire-exporter.xml</file>
8
        </fileset>
9
      </configuration>
10
    </facet>
11
  </component>
12
  <component name="NewModuleRootManager" LANGUAGE_LEVEL="JDK_1_8" inherit-compiler-output="false">
13
    <output url="file://$MODULE_DIR$/target/classes" />
14
    <output-test url="file://$MODULE_DIR$/target/test-classes" />
15
    <content url="file://$MODULE_DIR$">
16
      <sourceFolder url="file://$MODULE_DIR$/src/main/java" isTestSource="false" />
17
      <sourceFolder url="file://$MODULE_DIR$/src/main/resources" type="java-resource" />
18
      <sourceFolder url="file://$MODULE_DIR$/src/test/java" isTestSource="true" />
19
      <sourceFolder url="file://$MODULE_DIR$/src/test/resources" type="java-test-resource" />
20
      <excludeFolder url="file://$MODULE_DIR$/target" />
21
    </content>
22
    <orderEntry type="inheritedJdk" />
23
    <orderEntry type="sourceFolder" forTests="false" />
24
    <orderEntry type="library" name="Maven: org.springframework.boot:spring-boot-starter-web:1.5.2.RELEASE" level="project" />
25
    <orderEntry type="library" name="Maven: org.springframework.boot:spring-boot-starter:1.5.2.RELEASE" level="project" />
26
    <orderEntry type="library" name="Maven: org.springframework.boot:spring-boot:1.5.2.RELEASE" level="project" />
27
    <orderEntry type="library" name="Maven: org.springframework.boot:spring-boot-autoconfigure:1.5.2.RELEASE" level="project" />
28
    <orderEntry type="library" name="Maven: org.springframework.boot:spring-boot-starter-logging:1.5.2.RELEASE" level="project" />
29
    <orderEntry type="library" name="Maven: ch.qos.logback:logback-classic:1.1.11" level="project" />
30
    <orderEntry type="library" name="Maven: ch.qos.logback:logback-core:1.1.11" level="project" />
31
    <orderEntry type="library" name="Maven: org.slf4j:jcl-over-slf4j:1.7.24" level="project" />
32
    <orderEntry type="library" name="Maven: org.slf4j:jul-to-slf4j:1.7.24" level="project" />
33
    <orderEntry type="library" name="Maven: org.slf4j:log4j-over-slf4j:1.7.24" level="project" />
34
    <orderEntry type="library" scope="RUNTIME" name="Maven: org.yaml:snakeyaml:1.17" level="project" />
35
    <orderEntry type="library" name="Maven: org.springframework.boot:spring-boot-starter-tomcat:1.5.2.RELEASE" level="project" />
36
    <orderEntry type="library" name="Maven: org.apache.tomcat.embed:tomcat-embed-core:8.5.11" level="project" />
37
    <orderEntry type="library" name="Maven: org.apache.tomcat.embed:tomcat-embed-el:8.5.11" level="project" />
38
    <orderEntry type="library" name="Maven: org.apache.tomcat.embed:tomcat-embed-websocket:8.5.11" level="project" />
39
    <orderEntry type="library" name="Maven: org.hibernate:hibernate-validator:5.3.4.Final" level="project" />
40
    <orderEntry type="library" name="Maven: javax.validation:validation-api:1.1.0.Final" level="project" />
41
    <orderEntry type="library" name="Maven: org.jboss.logging:jboss-logging:3.3.0.Final" level="project" />
42
    <orderEntry type="library" name="Maven: com.fasterxml:classmate:1.3.3" level="project" />
43
    <orderEntry type="library" name="Maven: com.fasterxml.jackson.core:jackson-databind:2.8.7" level="project" />
44
    <orderEntry type="library" name="Maven: com.fasterxml.jackson.core:jackson-annotations:2.8.0" level="project" />
45
    <orderEntry type="library" name="Maven: com.fasterxml.jackson.core:jackson-core:2.8.7" level="project" />
46
    <orderEntry type="library" name="Maven: org.springframework:spring-web:4.3.7.RELEASE" level="project" />
47
    <orderEntry type="library" name="Maven: org.springframework:spring-aop:4.3.7.RELEASE" level="project" />
48
    <orderEntry type="library" name="Maven: org.springframework:spring-beans:4.3.7.RELEASE" level="project" />
49
    <orderEntry type="library" name="Maven: org.springframework:spring-context:4.3.7.RELEASE" level="project" />
50
    <orderEntry type="library" name="Maven: org.springframework:spring-webmvc:4.3.7.RELEASE" level="project" />
51
    <orderEntry type="library" name="Maven: org.springframework:spring-expression:4.3.7.RELEASE" level="project" />
52
    <orderEntry type="library" name="Maven: org.springframework.boot:spring-boot-starter-jdbc:1.5.2.RELEASE" level="project" />
53
    <orderEntry type="library" name="Maven: org.apache.tomcat:tomcat-jdbc:8.5.11" level="project" />
54
    <orderEntry type="library" name="Maven: org.apache.tomcat:tomcat-juli:8.5.11" level="project" />
55
    <orderEntry type="library" name="Maven: org.springframework:spring-jdbc:4.3.7.RELEASE" level="project" />
56
    <orderEntry type="library" name="Maven: org.springframework:spring-tx:4.3.7.RELEASE" level="project" />
57
    <orderEntry type="library" name="Maven: org.postgresql:postgresql:42.0.0" level="project" />
58
    <orderEntry type="library" name="Maven: org.apache.commons:commons-dbcp2:2.1" level="project" />
59
    <orderEntry type="library" name="Maven: org.apache.commons:commons-pool2:2.4.2" level="project" />
60
    <orderEntry type="library" name="Maven: commons-logging:commons-logging:1.2" level="project" />
61
    <orderEntry type="library" name="Maven: org.antlr:stringtemplate:3.2.1" level="project" />
62
    <orderEntry type="library" name="Maven: antlr:antlr:2.7.7" level="project" />
63
    <orderEntry type="library" name="Maven: org.apache.commons:commons-lang3:3.5" level="project" />
64
    <orderEntry type="library" name="Maven: commons-io:commons-io:2.5" level="project" />
65
    <orderEntry type="library" name="Maven: com.google.guava:guava:21.0" level="project" />
66
    <orderEntry type="library" name="Maven: net.sf.supercsv:super-csv:2.4.0" level="project" />
67
    <orderEntry type="library" name="Maven: com.google.code.gson:gson:2.8.0" level="project" />
68
    <orderEntry type="library" scope="TEST" name="Maven: org.springframework.boot:spring-boot-starter-test:1.5.2.RELEASE" level="project" />
69
    <orderEntry type="library" scope="TEST" name="Maven: org.springframework.boot:spring-boot-test:1.5.2.RELEASE" level="project" />
70
    <orderEntry type="library" scope="TEST" name="Maven: org.springframework.boot:spring-boot-test-autoconfigure:1.5.2.RELEASE" level="project" />
71
    <orderEntry type="library" scope="TEST" name="Maven: com.jayway.jsonpath:json-path:2.2.0" level="project" />
72
    <orderEntry type="library" scope="TEST" name="Maven: net.minidev:json-smart:2.2.1" level="project" />
73
    <orderEntry type="library" scope="TEST" name="Maven: net.minidev:accessors-smart:1.1" level="project" />
74
    <orderEntry type="library" scope="TEST" name="Maven: org.ow2.asm:asm:5.0.3" level="project" />
75
    <orderEntry type="library" name="Maven: org.slf4j:slf4j-api:1.7.24" level="project" />
76
    <orderEntry type="library" scope="TEST" name="Maven: junit:junit:4.12" level="project" />
77
    <orderEntry type="library" scope="TEST" name="Maven: org.assertj:assertj-core:2.6.0" level="project" />
78
    <orderEntry type="library" scope="TEST" name="Maven: org.mockito:mockito-core:1.10.19" level="project" />
79
    <orderEntry type="library" scope="TEST" name="Maven: org.objenesis:objenesis:2.1" level="project" />
80
    <orderEntry type="library" scope="TEST" name="Maven: org.hamcrest:hamcrest-core:1.3" level="project" />
81
    <orderEntry type="library" scope="TEST" name="Maven: org.hamcrest:hamcrest-library:1.3" level="project" />
82
    <orderEntry type="library" scope="TEST" name="Maven: org.skyscreamer:jsonassert:1.4.0" level="project" />
83
    <orderEntry type="library" scope="TEST" name="Maven: com.vaadin.external.google:android-json:0.0.20131108.vaadin1" level="project" />
84
    <orderEntry type="library" name="Maven: org.springframework:spring-core:4.3.7.RELEASE" level="project" />
85
    <orderEntry type="library" scope="TEST" name="Maven: org.springframework:spring-test:4.3.7.RELEASE" level="project" />
86
  </component>
87
</module>
modules/dnet-openaire-projects-exporter/trunk/src/test/java/eu/dnetlib/openaire/exporter/ProjectQueryParamsTest.java
1
package eu.dnetlib.openaire.exporter;
2

  
3
import org.junit.Before;
4
import org.junit.Test;
5

  
6
import static org.junit.Assert.assertEquals;
7

  
8
public class ProjectQueryParamsTest {
9

  
10
	private ProjectQueryParams queryParams;
11

  
12
	@Before
13
	public void setUp() throws Exception {
14
		queryParams = new ProjectQueryParams();
15

  
16
	}
17

  
18
	@Test
19
	public void testVerifyParamWhiteSpace() {
20
		queryParams.verifyParam("Discovery Projects");
21
	}
22

  
23
	@Test
24
	public void testVerifyParamPercentage() {
25
		queryParams.verifyParam("Discovery%20Projects");
26
	}
27

  
28
	@Test
29
	public void testVerifyDateParam(){
30
		String correctDate = "2012-03-04";
31
		assertEquals(correctDate, queryParams.verifyDateParam(correctDate));
32

  
33
	}
34

  
35
	@Test(expected=IllegalArgumentException.class)
36
	public void testVerifyDateParamException(){
37
		String wrongDate = "12-12-12";
38
		queryParams.verifyDateParam(wrongDate);
39

  
40
	}
41
}
modules/dnet-openaire-projects-exporter/trunk/src/test/java/eu/dnetlib/openaire/exporter/ProjectsControllerTest.java
1
package eu.dnetlib.openaire.exporter;
2

  
3
import java.io.IOException;
4

  
5
import org.antlr.stringtemplate.StringTemplate;
6
import org.apache.commons.io.IOUtils;
7
import org.junit.Before;
8
import org.junit.Test;
9
import org.springframework.core.io.ClassPathResource;
10
import org.springframework.core.io.Resource;
11

  
12
import static org.junit.Assert.assertEquals;
13

  
14
public class ProjectsControllerTest {
15

  
16
	private String queryTemplate = "/eu/dnetlib/openaire/exporter/sql/projects_fundings.sql.st";
17

  
18
	private Resource expectedQueryTemplate = new ClassPathResource("/eu/dnetlib/openaire/exporter/sql/expected_projects_fundings.sql.st");
19

  
20
	private ProjectsController controller;
21
	private ProjectQueryParams params;
22

  
23
	@Before
24
	public void setup() {
25
		controller = new ProjectsController();
26
		Resource template = new ClassPathResource(queryTemplate);
27

  
28
		controller.setProjectsFundingQueryTemplate(template);
29
		params = new ProjectQueryParams();
30
	}
31

  
32
	@Test
33
	public void testObtainFP7Query() throws IllegalArgumentException, IOException {
34
		params.setFundingProgramme("FP7");
35
		params.setFundingPath(null);
36
		String res = controller.obtainQuery(params);
37
		final StringTemplate st = new StringTemplate(IOUtils.toString(expectedQueryTemplate.getInputStream(),  ProjectsController.UTF8));
38
		st.setAttribute("fundingprefix", "ec__________::EC::FP7");
39
		System.out.println(res);
40
		System.out.println(st);
41
		assertEquals(st.toString(), res);
42
	}
43

  
44
	@Test
45
	public void testObtainFP7QuerySP1() throws IllegalArgumentException, IOException {
46
		params.setFundingProgramme("FP7");
47
		params.setFundingPath("SP1");
48
		String res = controller.obtainQuery(params);
49
		final StringTemplate st = new StringTemplate(IOUtils.toString(expectedQueryTemplate.getInputStream(),  ProjectsController.UTF8));
50
		st.setAttribute("fundingprefix", "ec__________::EC::FP7::SP1");
51
		System.out.println(res);
52
		assertEquals(st.toString(), res);
53
	}
54

  
55
	@Test
56
	public void testObtainFP7QueryHealth() throws IllegalArgumentException, IOException {
57
		params.setFundingProgramme("FP7");
58
		params.setFundingPath("SP1::HEALTH");
59
		String res = controller.obtainQuery(params);
60
		final StringTemplate st = new StringTemplate(IOUtils.toString(expectedQueryTemplate.getInputStream(),  ProjectsController.UTF8));
61
		st.setAttribute("fundingprefix", "ec__________::EC::FP7::SP1::HEALTH");
62
		System.out.println(res);
63
		assertEquals(st.toString(), res);
64
	}
65

  
66
	@Test
67
	public void testObtainFP7QueryHealth2() throws IllegalArgumentException, IOException {
68
		params.setFundingProgramme("FP7");
69
		params.setFundingPath("%::HEALTH");
70
		String res = controller.obtainQuery(params);
71
		final StringTemplate st = new StringTemplate(IOUtils.toString(expectedQueryTemplate.getInputStream(),  ProjectsController.UTF8));
72
		st.setAttribute("fundingprefix", "ec__________::EC::FP7::%::HEALTH");
73
		System.out.println(res);
74
		assertEquals(st.toString(), res);
75
	}
76

  
77
	@Test
78
	public void testObtainWellcomeTrustQuery() throws IllegalArgumentException, IOException {
79
		params.setFundingProgramme("WT");
80
		params.setFundingPath(null);
81
		String res = controller.obtainQuery(params);
82
		final StringTemplate st = new StringTemplate(IOUtils.toString(expectedQueryTemplate.getInputStream(),  ProjectsController.UTF8));
83
		st.setAttribute("fundingprefix", "wt__________::WT");
84
		System.out.println(res);
85
		assertEquals(st.toString(), res);
86
	}
87

  
88
	@Test
89
	public void testObtainFCTQuery() throws IllegalArgumentException, IOException {
90
		params.setFundingProgramme("FCT");
91
		params.setFundingPath(null);
92
		String res = controller.obtainQuery(params);
93
		final StringTemplate st = new StringTemplate(IOUtils.toString(expectedQueryTemplate.getInputStream(),  ProjectsController.UTF8));
94
		st.setAttribute("fundingprefix", "fct_________::FCT");
95
		System.out.println(res);
96
		assertEquals(st.toString(), res);
97
	}
98

  
99
	@Test
100
	public void testQueryWithDateParams() throws IllegalArgumentException, IOException {
101
		params.setFundingProgramme("WT");
102
		params.setFundingPath(null);
103
		params.setStartFrom("2015-05-04");
104
		String res = controller.obtainQuery(params);
105
		System.out.println(res);
106
		final StringTemplate st = new StringTemplate(IOUtils.toString(expectedQueryTemplate.getInputStream(),  ProjectsController.UTF8));
107
		st.setAttribute("fundingprefix", "wt__________::WT");
108
		String q = st.toString() + " AND startdate >= '2015-05-04'";
109
		assertEquals(q, res);
110
	}
111

  
112
	@Test
113
	public void testObtainSNSFQuery() throws IllegalArgumentException, IOException {
114
		params.setFundingProgramme("SNSF");
115
		params.setFundingPath(null);
116
		String res = controller.obtainQuery(params);
117
		final StringTemplate st = new StringTemplate(IOUtils.toString(expectedQueryTemplate.getInputStream(),  ProjectsController.UTF8));
118
		st.setAttribute("fundingprefix", "snsf________::SNSF");
119
		System.out.println(res);
120
		assertEquals(st.toString(), res);
121
	}
122

  
123
}
modules/dnet-openaire-projects-exporter/trunk/src/test/resources/eu/dnetlib/openaire/exporter/sql/expected_projects_fundings.sql.st
1
SELECT
2
	funder,
3
	jurisdiction,
4
	fundingpathid,
5
	acronym,
6
	title,
7
	code,
8
	startdate,
9
	enddate
10
FROM projects_api
11
WHERE fundingpathid like '$fundingprefix$%'
modules/dnet-openaire-projects-exporter/trunk/src/test/resources/eu/dnetlib/openaire/exporter/FP7-project.xml
1
<?xml version="1.0" encoding="UTF-8"?>
2
<ROW>
3
	<FIELD name="funder">EC</FIELD>
4
	<FIELD name="jurisdiction">EU</FIELD>
5
	<FIELD name="end_date">2012-07-31</FIELD>
6
	<FIELD name="grant_agreement_number">255646</FIELD>
7
	<FIELD name="title">Semiconductor lasers for generation of non-diffracting (Bessel) beams.</FIELD>
8
	<FIELD name="fundingpathid">ec__________::EC::FP7::SP3::PEOPLE</FIELD>
9
	<FIELD name="acronym">SENDBEAMS</FIELD>
10
	<FIELD name="start_date">2010-08-01</FIELD>
11
	<FIELD name="description">description of the funding path</FIELD>
12
</ROW> 
13

  
14
	
modules/dnet-openaire-projects-exporter/trunk/src/test/resources/eu/dnetlib/openaire/exporter/FCT-project.xml
1
<?xml version="1.0" encoding="UTF-8"?>
2
<ROW>
3
	<FIELD name="funder">FCT</FIELD>
4
	<FIELD name="jurisdiction">PT</FIELD>
5
	<FIELD name="end_date">2003-10-31</FIELD>
6
	<FIELD name="grant_agreement_number">32639</FIELD>
7
	<FIELD name="title">Social Classes and «lifestyles» in the city of Oporto</FIELD>
8
	<FIELD name="fundingpathid">fct_________::FCT::Orçamento de Funcionamento/POSC</FIELD>
9
	<FIELD name="acronym">PTDC/FIL-FIL/109889/2009</FIELD>
10
	<FIELD name="start_date">2000-11-01</FIELD>
11
	<FIELD name="description">description of the funding path</FIELD>
12
</ROW> 
modules/dnet-openaire-projects-exporter/trunk/src/test/resources/eu/dnetlib/openaire/exporter/SFI-project.xml
1
<?xml version="1.0" encoding="UTF-8"?>
2
<ROW>
3
	<FIELD name="funder">SFI</FIELD>
4
	<FIELD name="end_date">2016-08-31</FIELD>
5
	<FIELD name="grant_agreement_number">14/ADV/RC3022</FIELD>
6
	<FIELD name="title">Dr. Vesna Jaksic</FIELD>
7
	<FIELD name="acronym" isNull="true"/>
8
	<FIELD name="fundingpathid">sfi_________::SFI::Advance Award Programme</FIELD>
9
	<FIELD name="jurisdiction" isNull="true"/>
10
	<FIELD name="start_date">2014-09-01</FIELD>
11
</ROW>
modules/dnet-openaire-projects-exporter/trunk/src/test/resources/eu/dnetlib/openaire/exporter/MSES-project.xml
1
<?xml version="1.0" encoding="UTF-8"?>
2
<ROW>
3
	<FIELD name="funder">MSES</FIELD>
4
	<FIELD name="end_date">2009-12-31</FIELD>
5
	<FIELD name="grant_agreement_number">001-0000000-3177</FIELD>
6
	<FIELD name="title">Residence time of bacteria Escherichia coli in seawater and marine organisms</FIELD>
7
	<FIELD name="acronym" isNull="true"/>
8
	<FIELD name="fundingpathid">irb_hr______::MSES::fundingStream</FIELD>
9
	<FIELD name="jurisdiction">HR</FIELD>
10
	<FIELD name="start_date">2007-01-01</FIELD>
11
</ROW>
modules/dnet-openaire-projects-exporter/trunk/src/test/resources/eu/dnetlib/openaire/exporter/WT-project.xml
1
<ROW>
2
	<FIELD name="funder">WT</FIELD>
3
	<FIELD name="end_date">2013-07-29</FIELD>
4
	<FIELD name="grant_agreement_number">098241</FIELD>
5
	<FIELD name="title">EYEDIO DIGI - DEVELOPMENT OF AN INNOVATIVE, AFFORDABLE, EASY TO USE, HANDHELD RETINAL IMAGING PRODUCT FOR THE DIAGNOSIS OF DIABETIC RETINOPATHY.</FIELD>
6
	<FIELD name="acronym"></FIELD>
7
	<FIELD name="fundingpathid">wt__________::WT::Immunology and Infectious Disease</FIELD>
8
	<FIELD name="start_date">2012-01-30</FIELD>
9
	<FIELD name="jurisdiction"/>
10
	<FIELD name="description">description of the funding path</FIELD>
11
</ROW> 
modules/dnet-openaire-projects-exporter/trunk/src/main/java/eu/dnetlib/openaire/exporter/ProjectQueryParams.java
1
package eu.dnetlib.openaire.exporter;
2

  
3
import java.util.regex.Pattern;
4

  
5
import org.apache.commons.logging.Log;
6
import org.apache.commons.logging.LogFactory;
7

  
8
public class ProjectQueryParams {
9

  
10
	private String fundingProgramme = null;
11
	/** Whatever is following the fundingProgramme **/
12
	private String fundingPath = null;
13

  
14
	private String startFrom = null;
15
	private String startUntil = null;
16
	private String endFrom = null;
17
	private String endUntil = null;
18

  
19
	private static final Log log = LogFactory.getLog(ProjectQueryParams.class); // NOPMD by marko on 11/24/08 5:02 PM
20

  
21
	public String getFundingProgramme() {
22
		return fundingProgramme;
23
	}
24

  
25
	public void setFundingProgramme(final String fundingProgramme) {
26
		this.fundingProgramme = verifyParam(fundingProgramme);
27
	}
28

  
29
	public String getFundingPath() {
30
		return fundingPath;
31
	}
32

  
33
	public void setFundingPath(final String fundingPath) {
34
		this.fundingPath = verifyParam(fundingPath);
35
	}
36

  
37
	public String getStartFrom() {
38
		return startFrom;
39
	}
40

  
41
	public void setStartFrom(final String startFrom) {
42
		this.startFrom = verifyDateParam(startFrom);
43
	}
44

  
45
	public String getStartUntil() {
46
		return startUntil;
47
	}
48

  
49
	public void setStartUntil(final String startUntil) {
50
		this.startUntil = verifyDateParam(startUntil);
51
	}
52

  
53
	public String getEndFrom() {
54
		return endFrom;
55
	}
56

  
57
	public void setEndFrom(final String endFrom) {
58
		this.endFrom = verifyDateParam(endFrom);
59
	}
60

  
61
	public String getEndUntil() {
62
		return endUntil;
63
	}
64

  
65
	public void setEndUntil(final String endUntil) {
66
		this.endUntil = verifyDateParam(endUntil);
67
	}
68

  
69
	protected String verifyParam(final String p) {
70
		Pattern pattern = Pattern.compile("(\\w*(::|%| )*)*");
71
		log.debug("TESTING SQL PARAM: " + p);
72
		if ((p != null) && !pattern.matcher(p).matches()) throw new IllegalArgumentException("Parameter contains an invalid character");
73
		log.debug("TEST OK");
74
		return p;
75
	}
76

  
77
	protected String verifyDateParam(final String date){
78
		Pattern pattern = Pattern.compile("\\d\\d\\d\\d-\\d\\d-\\d\\d");
79
		log.debug("TESTING SQL DATE PARAM: " + date);
80
		if ((date != null) && !pattern.matcher(date).matches()) throw new IllegalArgumentException("Parameter date contains an invalid character. Accepted pattern is "+pattern.toString());
81
		log.debug("TEST OK");
82
		return date;
83

  
84
	}
85
}
modules/dnet-openaire-projects-exporter/trunk/src/main/java/eu/dnetlib/openaire/exporter/JdbcApiDao.java
1
package eu.dnetlib.openaire.exporter;
2

  
3
import java.io.IOException;
4
import java.io.OutputStream;
5
import java.nio.charset.Charset;
6
import java.sql.*;
7
import java.time.Duration;
8
import java.time.LocalDateTime;
9
import java.util.Arrays;
10
import java.util.List;
11
import java.util.zip.GZIPOutputStream;
12
import java.util.zip.ZipOutputStream;
13

  
14
import com.google.common.base.Function;
15
import com.google.common.base.Joiner;
16
import com.google.common.base.Splitter;
17
import com.google.common.collect.Iterables;
18
import com.google.common.collect.Lists;
19
import eu.dnetlib.openaire.exporter.tmpModel.Project;
20
import eu.dnetlib.openaire.exporter.tmpModel.ProjectDetail;
21
import org.antlr.stringtemplate.StringTemplate;
22
import org.apache.commons.dbcp2.BasicDataSource;
23
import org.apache.commons.lang3.StringUtils;
24
import org.apache.commons.logging.Log;
25
import org.apache.commons.logging.LogFactory;
26
import org.springframework.beans.factory.annotation.Autowired;
27
import org.springframework.beans.factory.annotation.Value;
28
import org.springframework.stereotype.Component;
29

  
30
/**
31
 * Created by claudio on 20/09/16.
32
 */
33
@Component
34
public class JdbcApiDao {
35

  
36
	public static final Charset UTF8 = Charset.forName("UTF-8");
37
	private static final Log log = LogFactory.getLog(JdbcApiDao.class);
38
	@Value("${openaire.exporter.jdbc.maxrows}")
39
	private int maxRows;
40

  
41
	@Value("${openaire.exporter.projectdetails.flushsize}")
42
	private int gzipFlushSize;
43

  
44
	@Value("${openaire.exporter.projects2tsv.fields}")
45
	private String tsvFields;
46

  
47
	@Autowired
48
	private BasicDataSource apiDataSource;
49

  
50
	public void streamProjects(final String sql, final OutputStream out,
51
			final String head, final StringTemplate projectTemplate, final String tail,
52
			final ValueCleaner cleaner) throws IOException, SQLException {
53

  
54
		if (log.isDebugEnabled()) {
55
			log.debug("Thread " + Thread.currentThread().getId() + " begin");
56
		}
57
		final LocalDateTime start = LocalDateTime.now();
58

  
59
		if (StringUtils.isNotBlank(head)) {
60
			out.write(head.getBytes(UTF8));
61
		}
62

  
63
		try(final Connection con = getConn(); final PreparedStatement stm = getStm(sql, con); final ResultSet rs = stm.executeQuery()) {
64
			while(rs.next()){
65
				final Project p = new Project()
66
						.setFunder(cleaner.clean(rs.getString("funder")))
67
						.setJurisdiction(cleaner.clean(rs.getString("jurisdiction")))
68
						.setFundingpathid(cleaner.clean(rs.getString("fundingpathid")))
69
						.setAcronym(cleaner.clean(rs.getString("acronym")))
70
						.setTitle(cleaner.clean(rs.getString("title")))
71
						.setCode(cleaner.clean(rs.getString("code")))
72
						.setStartdate(cleaner.clean(rs.getString("startdate")))
73
						.setEnddate(cleaner.clean(rs.getString("enddate")));
74

  
75
				projectTemplate.reset();
76
				projectTemplate.setAttribute("p", p);
77
				out.write(projectTemplate.toString().getBytes(UTF8));
78
			}
79
			if (StringUtils.isNotBlank(tail)) {
80
				out.write(tail.getBytes(UTF8));
81
			}
82
			final LocalDateTime end = LocalDateTime.now();
83
			if (log.isDebugEnabled()) {
84
				log.debug("Thread " + Thread.currentThread().getId() + " ends, took: " + Duration.between(start, end));
85
			}
86
		} finally {
87
			out.close();
88
		}
89
	}
90

  
91
	public void streamProjectsTSV(final String sql, final ZipOutputStream out) throws IOException, SQLException {
92

  
93
		if (log.isDebugEnabled()) {
94
			log.debug("Thread " + Thread.currentThread().getId() + " begin");
95
		}
96
		final LocalDateTime start = LocalDateTime.now();
97
		final List<String> fields = Lists.newArrayList(Splitter.on(",").omitEmptyStrings().trimResults().split(tsvFields));
98
		writeCSVLine(out, fields);
99
		try(final Connection con = getConn(); final PreparedStatement stm = getStm(sql, con); final ResultSet rs = stm.executeQuery()) {
100
			while(rs.next()) {
101
				final Project p = new Project()
102
						.setCode(rs.getString("code"))
103
						.setAcronym(rs.getString("acronym"))
104
						.setTitle(rs.getString("title"))
105
						.setCallIdentifier(rs.getString("call_identifier"))
106
						.setStartdate(rs.getString("startdate"))
107
						.setEnddate(rs.getString("enddate"))
108
						.setOaMandateForPublications(rs.getBoolean("oa_mandate_for_publications"))
109
						.setOaMandateForDatasets(rs.getBoolean("oa_mandate_for_datasets"))
110
						.setDescription(rs.getString("description"))
111
						.setOrgLegalname(rs.getString("legalname"))
112
						.setOrgCountry(rs.getString("country"))
113
						.setOrgRole(rs.getString("role"))
114
						.setFirstname(rs.getString("firstname"))
115
						.setSecondnames(rs.getString("secondnames"))
116
						.setEmail(rs.getString("email"));
117

  
118
				writeCSVLine(out, p.asList());
119
			}
120
			out.closeEntry();
121
			final LocalDateTime end = LocalDateTime.now();
122
			if (log.isDebugEnabled()) {
123
				log.debug("Thread " + Thread.currentThread().getId() + " ends, took: " + Duration.between(start, end));
124
			}
125
		} finally {
126
			out.close();
127
		}
128
	}
129

  
130
	private void writeCSVLine(final ZipOutputStream out, final List<String> list) throws IOException {
131
		out.write(Joiner.on('\t').useForNull("").join(list).getBytes(UTF8));
132
		out.write('\n');
133
	}
134

  
135

  
136
	public void streamProjectDetails(final String sql, final OutputStream out, final String format) throws SQLException, IOException {
137
		if (log.isDebugEnabled()) {
138
			log.debug("Thread " + Thread.currentThread().getId() + " begin");
139
		}
140
		final LocalDateTime start = LocalDateTime.now();
141
		int i = 0;
142
		try(final Connection con = getConn(); final PreparedStatement stm = getStm(sql, con); final ResultSet rs = stm.executeQuery()) {
143
			while (rs.next()) {
144
				final ProjectDetail p = getProjectDetail(rs);
145

  
146
				switch (format) {
147
				case "csv":
148
					out.write(p.asCSV().getBytes(UTF8));
149
					break;
150
				case "json":
151
					out.write(p.asJson().getBytes(UTF8));
152
					break;
153
				}
154
				if (++i % gzipFlushSize == 0) {
155
					log.debug("flushing output stream");
156
					out.flush();
157
				}
158
			}
159
			final LocalDateTime end = LocalDateTime.now();
160
			if (log.isDebugEnabled()) {
161
				log.debug("Thread " + Thread.currentThread().getId() + " ends, took: " + Duration.between(start, end));
162
			}
163
		} finally {
164
			if (out instanceof GZIPOutputStream) {
165
				((GZIPOutputStream) out).finish();
166
			}
167
			out.close();
168
		}
169
	}
170

  
171
	private Connection getConn() throws SQLException {
172
		final Connection conn = apiDataSource.getConnection();
173
		conn.setAutoCommit(false);
174
		return conn;
175
	}
176

  
177
	private PreparedStatement getStm(final String sql, final Connection con) throws SQLException {
178
		final PreparedStatement stm = con.prepareStatement(sql);
179
		stm.setFetchSize(maxRows);
180
		return stm;
181
	}
182

  
183
	private ProjectDetail getProjectDetail(final ResultSet rs) throws SQLException {
184
		return new ProjectDetail()
185
				.setProjectId(rs.getString("projectid"))
186
				.setAcronym(rs.getString("acronym"))
187
				.setCode(rs.getString("code"))
188
				.setJsonextrainfo(rs.getString("jsonextrainfo"))
189
				.setFundingPath(asList(rs.getArray("fundingpath")));
190
	}
191

  
192
	private List<String> asList(final Array value) throws SQLException {
193
		if (value != null) {
194
			final List<Object> list = Arrays.asList((Object[]) value.getArray());
195
			return Lists.newArrayList(Iterables.transform(list, new Function<Object, String>() {
196
				@Override
197
				public String apply(final Object o) {
198
					return o != null ? o.toString() : null;
199

  
200
				}
201
			}));
202
		}
203
		return Lists.newArrayList();
204
	}
205

  
206
}
modules/dnet-openaire-projects-exporter/trunk/src/main/java/eu/dnetlib/openaire/exporter/tmpModel/Project.java
1
package eu.dnetlib.openaire.exporter.tmpModel;
2

  
3
import java.util.ArrayList;
4
import java.util.List;
5

  
6
import com.google.common.base.Splitter;
7
import com.google.common.collect.Lists;
8
import org.apache.commons.lang3.StringUtils;
9

  
10
/**
11
 * Created by claudio on 20/09/16.
12
 */
13
public class Project {
14

  
15
	public static final String INFO_EU_REPO_GRANT_AGREEMENT = "info:eu-repo/grantAgreement/";
16
	private String code;
17
	private String acronym;
18
	private String title;
19
	private String callIdentifier;
20
	private String startdate;
21
	private String enddate;
22
	private boolean oaMandateForPublications;
23
	private boolean oaMandateForDatasets;
24
	private String fundingpathid;
25
	private String description;
26
	private String funder;
27
	private String jurisdiction;
28
	private String orgLegalname;
29
	private String orgCountry;
30
	private String orgRole;
31
	private String firstname;
32
	private String secondnames;
33
	private String email;
34

  
35
	public Project() {
36
	}
37

  
38
	public String getIdnamespace() {
39
		String res = INFO_EU_REPO_GRANT_AGREEMENT + getFunder()+"/";
40
		final String fundingProgram = asFundingProgram(getFundingpathid());
41
		if (StringUtils.isNotBlank(fundingProgram)) {
42
			res += fundingProgram;
43
		}
44
		res += "/" + escapeCode(getCode());
45
		if (StringUtils.isNotBlank(getJurisdiction())) {
46
			res += "/" + getJurisdiction();
47
		}
48
		return res;
49
	}
50

  
51
	public String getListLabel() {
52
		return String.format("for:value:component:_%s_project_id", asFunder(getFunder()));
53
	}
54

  
55
	private String asFunder(final String legalshortname) {
56
		switch (legalshortname.toLowerCase()) {
57
		case "ec":
58
			return asFundingProgram(getFundingpathid()).toLowerCase();
59
		default:
60
			return legalshortname.toLowerCase();
61
		}
62
	}
63

  
64
	public List<String> asList() {
65
		return Lists.newArrayList(
66
				clean(getCode()),
67
				clean(getAcronym()),
68
				clean(getTitle()),
69
				clean(getCallIdentifier()),
70
				clean(getStartdate()),
71
				clean(getEnddate()),
72
				String.valueOf(isOaMandateForPublications()),
73
				String.valueOf(isOaMandateForDatasets()),
74
				clean(getDescription()),
75
				clean(getOrgLegalname()),
76
				clean(getOrgCountry()),
77
				clean(getOrgRole()),
78
				clean(getFirstname()),
79
				clean(getSecondnames()),
80
				clean(getEmail()));
81
	}
82

  
83
	private String clean(final String s) {
84
		return StringUtils.isNotBlank(s) ? "\"" + s.replaceAll("\\n|\\t|\\s+", " ").replace("\"","\"\"").trim() + "\"" : "";
85
	}
86

  
87
	private String escapeCode(final String code) {
88
		return replaceSlash(code);
89
	}
90

  
91
	private String asFundingProgram(final String fundingpathid) {
92
		final ArrayList<String> strings = Lists.newArrayList(Splitter.on("::").split(fundingpathid));
93
		if(strings.size() <= 1) throw new IllegalStateException("Unexpected funding id: "+fundingpathid);
94
		if(strings.size() == 2) return "";
95
		else return replaceSlash(strings.get(2));
96
	}
97

  
98
	private String replaceSlash(final String s) {
99
		return s.replaceAll("/", "%2F");
100
	}
101

  
102
	public String getCode() {
103
		return code;
104
	}
105

  
106
	public Project setCode(final String code) {
107
		this.code = code;
108
		return this;
109
	}
110

  
111
	public String getAcronym() {
112
		return acronym;
113
	}
114

  
115
	public Project setAcronym(final String acronym) {
116
		this.acronym = acronym;
117
		return this;
118
	}
119

  
120
	public String getTitle() {
121
		return title;
122
	}
123

  
124
	public Project setTitle(final String title) {
125
		this.title = title;
126
		return this;
127
	}
128

  
129
	public String getCallIdentifier() {
130
		return callIdentifier;
131
	}
132

  
133
	public Project setCallIdentifier(final String call_identifier) {
134
		this.callIdentifier = call_identifier;
135
		return this;
136
	}
137

  
138
	public String getStartdate() {
139
		return startdate;
140
	}
141

  
142
	public Project setStartdate(final String startdate) {
143
		this.startdate = startdate;
144
		return this;
145
	}
146

  
147
	public String getEnddate() {
148
		return enddate;
149
	}
150

  
151
	public Project setEnddate(final String enddate) {
152
		this.enddate = enddate;
153
		return this;
154
	}
155

  
156
	public boolean isOaMandateForPublications() {
157
		return oaMandateForPublications;
158
	}
159

  
160
	public Project setOaMandateForPublications(final boolean oaMandateForPublications) {
161
		this.oaMandateForPublications = oaMandateForPublications;
162
		return this;
163
	}
164

  
165
	public boolean isOaMandateForDatasets() {
166
		return oaMandateForDatasets;
167
	}
168

  
169
	public Project setOaMandateForDatasets(final boolean oaMandateForDatasets) {
170
		this.oaMandateForDatasets = oaMandateForDatasets;
171
		return this;
172
	}
173

  
174

  
175
	public String getFundingpathid() {
176
		return fundingpathid;
177
	}
178

  
179
	public Project setFundingpathid(final String fundingpathid) {
180
		this.fundingpathid = fundingpathid;
181
		return this;
182
	}
183

  
184
	public String getDescription() {
185
		return description;
186
	}
187

  
188
	public Project setDescription(final String description) {
189
		this.description = description;
190
		return this;
191
	}
192

  
193
	public String getJurisdiction() {
194
		return jurisdiction;
195
	}
196

  
197
	public Project setJurisdiction(final String jurisdiction) {
198
		this.jurisdiction = jurisdiction;
199
		return this;
200
	}
201

  
202
	public String getOrgLegalname() {
203
		return orgLegalname;
204
	}
205

  
206
	public Project setOrgLegalname(final String legalname) {
207
		this.orgLegalname = legalname;
208
		return this;
209
	}
210

  
211
	public String getOrgCountry() {
212
		return orgCountry;
213
	}
214

  
215
	public Project setOrgCountry(final String country) {
216
		this.orgCountry = country;
217
		return this;
218
	}
219

  
220
	public String getOrgRole() {
221
		return orgRole;
222
	}
223

  
224
	public Project setOrgRole(final String role) {
225
		this.orgRole = role;
226
		return this;
227
	}
228

  
229
	public String getFirstname() {
230
		return firstname;
231
	}
232

  
233
	public Project setFirstname(final String firstname) {
234
		this.firstname = firstname;
235
		return this;
236
	}
237

  
238
	public String getSecondnames() {
239
		return secondnames;
240
	}
241

  
242
	public Project setSecondnames(final String secondnames) {
243
		this.secondnames = secondnames;
244
		return this;
245
	}
246

  
247
	public String getEmail() {
248
		return email;
249
	}
250

  
251
	public Project setEmail(final String email) {
252
		this.email = email;
253
		return this;
254
	}
255

  
256
	public String getFunder() {
257
		return funder;
258
	}
259

  
260
	public Project setFunder(final String funder) {
261
		this.funder = funder;
262
		return this;
263
	}
264
}
modules/dnet-openaire-projects-exporter/trunk/src/main/java/eu/dnetlib/openaire/exporter/tmpModel/ProjectDetail.java
1
package eu.dnetlib.openaire.exporter.tmpModel;
2

  
3
import java.io.IOException;
4
import java.io.StringReader;
5
import java.io.StringWriter;
6
import java.util.List;
7

  
8
import com.google.gson.Gson;
9
import org.supercsv.cellprocessor.Optional;
10
import org.supercsv.cellprocessor.ift.CellProcessor;
11
import org.supercsv.cellprocessor.ift.StringCellProcessor;
12
import org.supercsv.io.CsvBeanReader;
13
import org.supercsv.io.CsvBeanWriter;
14
import org.supercsv.io.ICsvBeanReader;
15
import org.supercsv.io.ICsvBeanWriter;
16
import org.supercsv.prefs.CsvPreference;
17
import org.supercsv.util.CsvContext;
18

  
19
/**
20
 * Created by claudio on 22/09/16.
21
 */
22
public class ProjectDetail {
23

  
24
	private static final String[] NAMEMAPPING = { "projectId", "acronym", "code", "jsonextrainfo", "fundingPath" };
25

  
26
	private String projectId;
27
	private String acronym;
28
	private String code;
29
	private String jsonextrainfo;
30
	private List<String> fundingPath;
31

  
32
	public ProjectDetail() {}
33

  
34
	public static ProjectDetail fromJson(final String json) {
35
		return new Gson().fromJson(json, ProjectDetail.class);
36
	}
37

  
38
	public static ProjectDetail fromCSV(final String csv) throws IOException {
39
		ICsvBeanReader beanReader = null;
40
		try {
41
			beanReader = new CsvBeanReader(new StringReader(csv), CsvPreference.STANDARD_PREFERENCE);
42
			return beanReader.read(ProjectDetail.class, NAMEMAPPING, getProcessors(new StringCellProcessor() {
43
				@Override
44
				public Object execute(final Object value, final CsvContext context) {
45
					return new Gson().fromJson(value.toString(), List.class);
46
				}
47
			}));
48
		} finally {
49
			if (beanReader != null) {
50
				beanReader.close();
51
			}
52
		}
53
	}
54

  
55
	/**
56
	 * Sets up the processors used for the examples. There are 10 CSV columns, so 10 processors are defined. Empty
57
	 * columns are read as null (hence the NotNull() for mandatory columns).
58
	 *
59
	 * @return the cell processors
60
	 */
61
	private static CellProcessor[] getProcessors(final CellProcessor fundingPathProcessor) {
62
		return new CellProcessor[] {
63
				new Optional(), // projectId
64
				new Optional(), // acronym
65
				new Optional(), // code
66
				new Optional(), // jsonextrainfo
67
				fundingPathProcessor
68
		};
69
	}
70

  
71
	public String asJson() {
72
		return new Gson().toJson(this) + '\n';
73
	}
74

  
75
	public String asCSV() throws IOException {
76
		final StringWriter sb = new StringWriter();
77
		try (ICsvBeanWriter beanWriter = new CsvBeanWriter(sb, CsvPreference.STANDARD_PREFERENCE)) {
78
			beanWriter.write(this, NAMEMAPPING, getProcessors(new StringCellProcessor() {
79
				@Override
80
				public Object execute(final Object value, final CsvContext context) {
81
					return new Gson().toJson(value);
82
				}
83
			}));
84
			beanWriter.flush();
85
		}
86

  
87
		return sb.toString();
88
	}
89

  
90
	public String getProjectId() {
91
		return projectId;
92
	}
93

  
94
	public ProjectDetail setProjectId(final String projectId) {
95
		this.projectId = projectId;
96
		return this;
97
	}
98

  
99
	public String getAcronym() {
100
		return acronym;
101
	}
102

  
103
	public ProjectDetail setAcronym(final String acronym) {
104
		this.acronym = acronym;
105
		return this;
106
	}
107

  
108
	public String getCode() {
109
		return code;
110
	}
111

  
112
	public ProjectDetail setCode(final String code) {
113
		this.code = code;
114
		return this;
115
	}
116

  
117
	public String getJsonextrainfo() {
118
		return jsonextrainfo;
119
	}
120

  
121
	public ProjectDetail setJsonextrainfo(final String jsonextrainfo) {
122
		this.jsonextrainfo = jsonextrainfo;
123
		return this;
124
	}
125

  
126
	public List<String> getFundingPath() {
127
		return fundingPath;
128
	}
129

  
130
	public ProjectDetail setFundingPath(final List<String> fundingPath) {
131
		this.fundingPath = fundingPath;
132
		return this;
133
	}
134

  
135
}
modules/dnet-openaire-projects-exporter/trunk/src/main/java/eu/dnetlib/openaire/exporter/ValueCleaner.java
1
package eu.dnetlib.openaire.exporter;
2

  
3
/**
4
 * Created by claudio on 23/09/2016.
5
 */
6
public interface ValueCleaner {
7

  
8
	public String clean(String s);
9

  
10
}
modules/dnet-openaire-projects-exporter/trunk/src/main/java/eu/dnetlib/openaire/exporter/ProjectQueryParamsFactory.java
1
package eu.dnetlib.openaire.exporter;
2

  
3
import javax.servlet.http.HttpServletRequest;
4

  
5
import org.springframework.stereotype.Component;
6

  
7
@Component
8
public class ProjectQueryParamsFactory {
9

  
10
	private static final String BASE_PATH = "/openaire/export/";
11
	private static final String NO_FILTER = "ALL";
12

  
13
	public ProjectQueryParams generateParams(final HttpServletRequest request,
14
			final String startFrom,
15
			final String startUntil,
16
			final String endFrom,
17
			final String endUntil) {
18
		ProjectQueryParams params = new ProjectQueryParams();
19

  
20
		String[] arr = request.getPathInfo().replace(BASE_PATH, "").split("\\/");
21
		if (arr.length != 5) throw new IllegalArgumentException("Invalid url");
22

  
23
		params.setFundingProgramme(arr[0]);
24
		String stream = NO_FILTER.equals(arr[1]) ? null : arr[1];
25
		String substream = NO_FILTER.equals(arr[2]) ? null : arr[2];
26
		if (substream == null) {
27
			params.setFundingPath(stream);
28
		} else {
29
			if (stream == null) {
30
				stream = "%";
31
			}
32
			params.setFundingPath(stream + "::" + substream);
33
		}
34
		// params.setSpecificProgramme(NO_FILTER.equals(arr[1]) ? null : arr[1]);
35
		// params.setSubdivision(NO_FILTER.equals(arr[2]) ? null : arr[2]);
36
		// NB: arr[3] should be 'projects'
37
		// NB: arr[4] should be '[file].do'
38
		params.setStartFrom(startFrom);
39
		params.setStartUntil(startUntil);
40
		params.setEndFrom(endFrom);
41
		params.setEndUntil(endUntil);
42

  
43
		return params;
44
	}
45
}
modules/dnet-openaire-projects-exporter/trunk/src/main/java/eu/dnetlib/openaire/exporter/ProjectsController.java
1
package eu.dnetlib.openaire.exporter;
2

  
3
import java.io.BufferedOutputStream;
4
import java.io.IOException;
5
import java.io.OutputStream;
6
import java.sql.SQLException;
7
import java.text.SimpleDateFormat;
8
import java.util.Arrays;
9
import java.util.Date;
10
import java.util.Map;
11
import java.util.zip.GZIPOutputStream;
12
import java.util.zip.ZipEntry;
13
import java.util.zip.ZipOutputStream;
14
import javax.servlet.ServletOutputStream;
15
import javax.servlet.ServletResponse;
16
import javax.servlet.http.HttpServletRequest;
17
import javax.servlet.http.HttpServletResponse;
18

  
19
import com.google.common.collect.Maps;
20
import com.google.common.xml.XmlEscapers;
21
import org.apache.commons.io.IOUtils;
22
import org.apache.commons.lang3.StringUtils;
23
import org.apache.commons.lang3.exception.ExceptionUtils;
24
import org.apache.commons.logging.Log;
25
import org.apache.commons.logging.LogFactory;
26
import org.springframework.beans.factory.annotation.Autowired;
27
import org.springframework.beans.factory.annotation.Value;
28
import org.springframework.core.io.Resource;
29
import org.springframework.http.HttpStatus;
30
import org.springframework.stereotype.Controller;
31
import org.springframework.web.bind.annotation.*;
32
import org.antlr.stringtemplate.StringTemplate;
33

  
34
@Controller
35
public class ProjectsController {
36

  
37
	private static final Log log = LogFactory.getLog(ProjectsController.class); // NOPMD by marko on 11/24/08 5:02 PM
38

  
39
	public final static String UTF8 = "UTF-8";
40

  
41
	@Value("${openaire.exporter.projectsfundings.sql.template}")
42
	private Resource projectsFundingQueryTemplate;
43

  
44
	@Value("${openaire.exporter.projects2tsv.sql.template}")
45
	private Resource projects2tsvQueryTemplate;
46

  
47
	@Value("${openaire.exporter.dspace.template.project}")
48
	private Resource dspaceTemplate;
49

  
50
	@Value("${openaire.exporter.dspace.template.head}")
51
	private Resource dspaceHeadTemplate;
52

  
53
	@Value("${openaire.exporter.dspace.template.tail}")
54
	private Resource dspaceTailTemplate;
55

  
56
	@Value("${openaire.exporter.eprints.template}")
57
	private Resource eprintsTemplate;
58

  
59
	@Value("${openaire.exporter.projectdetails.sql}")
60
	private Resource projectDetailsSql;
61

  
62
	@Autowired
63
	private JdbcApiDao dao;
64

  
65
	@Autowired
66
	private ProjectQueryParamsFactory projectQueryParamsFactory;
67

  
68
	@Deprecated
69
	/**
70
	 * @deprecated  we should use the codes returned by the yet to come service
71
	 */
72
	public enum Funding {
73
		FP7, WT, FCT, H2020, NHMRC, ARC, SFI, MZOS, HRZZ, NWO, MESTD, FWF, SNSF, RCUK
74
	}
75

  
76
	@RequestMapping(value = "/openaire/export/**/project/dspace.do")
77
	void processDspace(final HttpServletRequest request,
78
			final ServletResponse response,
79
			@RequestParam(value = "startFrom", required = false) final String startFrom,
80
			@RequestParam(value = "startUntil", required = false) final String startUntil,
81
			@RequestParam(value = "endFrom", required = false) final String endFrom,
82
			@RequestParam(value = "endUntil", required = false) final String endUntil) throws Exception {
83

  
84

  
85
		final ProjectQueryParams params = projectQueryParamsFactory.generateParams(request, startFrom, startUntil, endFrom, endUntil);
86
		final StringTemplate headSt = new StringTemplate(IOUtils.toString(dspaceHeadTemplate.getInputStream(), UTF8 ));
87

  
88
		headSt.setAttribute("fundingProgramme", params.getFundingProgramme());
89

  
90
		final StringTemplate tailSt = new StringTemplate(IOUtils.toString(dspaceTailTemplate.getInputStream(), UTF8));
91

  
92
		response.setContentType("text/xml");
93
		doProcess(response, params, headSt.toString(), dspaceTemplate, tailSt.toString(), s -> XmlEscapers.xmlContentEscaper().escape(oneLiner(s)));
94
	}
95

  
96
	@RequestMapping(value = "/openaire/export/**/project/eprints.do")
97
	void processEprints(final HttpServletRequest request,
98
			final ServletResponse response,
99
			@RequestParam(value = "startFrom", required = false) final String startFrom,
100
			@RequestParam(value = "startUntil", required = false) final String startUntil,
101
			@RequestParam(value = "endFrom", required = false) final String endFrom,
102
			@RequestParam(value = "endUntil", required = false) final String endUntil) throws Exception {
103

  
104
		final ProjectQueryParams params = projectQueryParamsFactory.generateParams(request, startFrom, startUntil, endFrom, endUntil);
105
		response.setContentType("text/html");
106
		doProcess(response, params, null, eprintsTemplate, null, s -> oneLiner(s));
107
	}
108

  
109
	private String oneLiner(final String s) {
110
		return StringUtils.isNotBlank(s) ? s.replaceAll("\\n", " ").trim() : "";
111
	}
112

  
113
	private void doProcess(
114
			final ServletResponse response,
115
			final ProjectQueryParams params,
116
			final String head, final Resource projectTemplate, final String tail,
117
			final ValueCleaner cleaner) throws IOException, SQLException {
118

  
119
		final StringTemplate st = new StringTemplate(IOUtils.toString(projectTemplate.getInputStream(), UTF8));
120
		dao.streamProjects(obtainQuery(params), response.getOutputStream(), head, st, tail, cleaner);
121
	}
122

  
123
	@RequestMapping(value = "/openaire/export/project2tsv.do")
124
	void processTsv(final HttpServletRequest request, final HttpServletResponse response,
125
			@RequestParam(value = "funding", required = true) final String funding,
126
			@RequestParam(value = "article293", required = false) final Boolean article293) throws Exception {
127

  
128
		final String date = new SimpleDateFormat("yyyyMMdd").format(new Date());
129
		final String filename = "projects_" + funding + "_" + date + ".tsv";
130
		response.setContentType("text/tab-separated-values");
131
		response.setHeader("Content-Disposition", "attachment; filename=\"" + filename + ".zip\"");
132

  
133
		final StringTemplate st = new StringTemplate(IOUtils.toString(projects2tsvQueryTemplate.getInputStream(), UTF8));
134
		Funding requestedFunding = Funding.valueOf(funding.toUpperCase());
135
		String fundingPrefix = getFundingPrefix(requestedFunding, null);
136
		log.debug("Setting fundingprefix to " + fundingPrefix);
137
		st.setAttribute("fundingprefix", fundingPrefix);
138
		st.setAttribute("filters", expandFilters(article293));
139

  
140
		final ZipOutputStream out = new ZipOutputStream(new BufferedOutputStream(response.getOutputStream()));
141
		out.putNextEntry(new ZipEntry(filename));
142
		dao.streamProjectsTSV(st.toString(), out);
143
	}
144

  
145
	@RequestMapping(value = "/openaire/export/streamProjectDetails.do")
146
	void streamProjectDetails(final HttpServletResponse response,
147
			@RequestParam(value = "format", required = true) final String format,
148
			@RequestParam(value = "compress", required = false) final Boolean compress) throws IOException, SQLException {
149
		final String sql = IOUtils.toString(projectDetailsSql.getInputStream(), UTF8);
150

  
151
		if (compress != null && compress) {
152
			response.setHeader("Content-Encoding", "gzip");
153
		}
154
		switch (format) {
155
		case "csv":
156
			response.setContentType("text/csv");
157
			break;
158
		case "json":
159
			response.setContentType("text/plain");
160
			break;
161
		default: throw new IllegalArgumentException("unsupported format: " + format);
162
		}
163

  
164
		final OutputStream outputStream = getOutputStream(response.getOutputStream(), compress);
165
		dao.streamProjectDetails(sql, outputStream, format);
166
	}
167

  
168
	private OutputStream getOutputStream(final ServletOutputStream outputStream, final Boolean compress) throws IOException {
169
		if (compress != null && compress) {
170
			return new GZIPOutputStream(outputStream);
171
		}
172
		return outputStream;
173
	}
174

  
175
	@ExceptionHandler({Exception.class, Throwable.class})
176
	@ResponseStatus(value = HttpStatus.INTERNAL_SERVER_ERROR)
177
	public @ResponseBody ErrorMessage handleSqlException(final Exception e) {
178
		log.error(e.getMessage());
179
		return new ErrorMessage(e);
180
	}
181

  
182
	public class ErrorMessage {
183

  
184
		private final String message;
185
		private final String stacktrace;
186

  
187
		public ErrorMessage(final Exception e) {
188
			this(e.getMessage(), ExceptionUtils.getStackTrace(e));
189
		}
190

  
191
		public ErrorMessage(final String message, final String stacktrace) {
192
			this.message = message;
193
			this.stacktrace = stacktrace;
194
		}
195

  
196
		public String getMessage() {
197
			return this.message;
198
		}
199

  
200
		public String getStacktrace() {
201
			return this.stacktrace;
202
		}
203
	}
204

  
205

  
206
	private Map<String, String> expandFilters(final Boolean article293) {
207
		final Map<String, String> res = Maps.newHashMap();
208

  
209
		if (article293 != null) {
210
			res.put("ec_article29_3", String.valueOf(article293));
211
		}
212

  
213
		return res;
214
	}
215

  
216
	/**
217
	 * Creates the query on the fundingProgramme specified in the given parameters.
218
	 *
219
	 * @param params
220
	 *            request parameters
221
	 * @return the query string
222
	 * @throws IllegalArgumentException
223
	 *             if the funding program is not recognized
224
	 * @throws IOException
225
	 *             if there are problem loading the query temlate
226
	 * @throws IllegalArgumentException
227
	 *             if the funding program is not recognized
228
	 */
229
	protected String obtainQuery(final ProjectQueryParams params) throws IllegalArgumentException, IOException {
230
		String funding = params.getFundingProgramme();
231
		String suffix = params.getFundingPath();
232
		String fundingPrefix = getFundingPrefix(Funding.valueOf(funding.toUpperCase()), suffix);
233
		final StringTemplate st = new StringTemplate(IOUtils.toString(projectsFundingQueryTemplate.getInputStream(), UTF8));
234
		st.setAttribute("fundingprefix", fundingPrefix);
235
		String theQuery = setDateParameters(st.toString(), params);
236
		log.debug("Generated query: " + theQuery);
237
		return theQuery;
238
	}
239

  
240
	/**
241
	 * @deprecated call the yet to come service that returns the funding prefix given a funding code
242
	 */
243
	@Deprecated
244
	private String getFundingPrefix(final Funding funding, final String suffix) throws IllegalArgumentException {
245
		switch (funding) {
246
		case FCT:
247
			if (StringUtils.isBlank(suffix)) return "fct_________::FCT";
248
			else return "fct_________::FCT::" + suffix;
249
		case WT:
250
			if (StringUtils.isBlank(suffix)) return "wt__________::WT";
251
			else return "wt__________::WT::" + suffix;
252
		case FP7:
253
			if (StringUtils.isBlank(suffix)) return "ec__________::EC::FP7";
254
			else return "ec__________::EC::FP7::" + suffix;
255
		case H2020:
256
			if (StringUtils.isBlank(suffix)) return "ec__________::EC::H2020";
257
			else return "ec__________::EC::H2020" + suffix;
258
		case NHMRC:
259
			if (StringUtils.isBlank(suffix)) return "nhmrc_______::NHMRC";
260
			else return "nhmrc_______::NHMRC::" + suffix;
261
		case ARC:
262
			if (StringUtils.isBlank(suffix)) return "arc_________::ARC";
263
			else return "arc_________::ARC::" + suffix;
264
		case SFI:
265
			if (StringUtils.isBlank(suffix)) return "sfi_________::SFI";
266
			else return "sfi_________::SFI::" + suffix;
267
		case MZOS:
268
			if (StringUtils.isBlank(suffix)) return "irb_hr______::MZOS";
269
			else return "irb_hr______::MZOS::" + suffix;
270
		case HRZZ:
271
			if (StringUtils.isBlank(suffix)) return "irb_hr______::HRZZ";
272
			else return "irb_hr______::HRZZ::" + suffix;
273
		case NWO:
274
			if (StringUtils.isBlank(suffix)) return "nwo_________::NWO";
275
			else return "nwo_________::NWO::" + suffix;
276
		case MESTD:
277
			if (StringUtils.isBlank(suffix)) return "mestd_______::MESTD";
278
			else return "mestd_______::MESTD::" + suffix;
279
		case FWF:
280
			if (StringUtils.isBlank(suffix)) return "fwf_________::FWF";
281
			else return "fwf_________::FWF::" + suffix;
282
		case SNSF:
283
			if (StringUtils.isBlank(suffix)) return "snsf________::SNSF";
284
			else return "snsf________::SNSF::" + suffix;
285
		case RCUK:
286
			if (StringUtils.isBlank(suffix)) return "rcuk________::RCUK";
287
			else return "rcuk________::RCUK::" + suffix;
288
		default:
289
			throw new IllegalArgumentException("Invalid funding " + funding + " (valid are: " + Arrays.asList(Funding.values()) + ") ");
290
		}
291
	}
292

  
293
	private String setDateParameters(final String query, final ProjectQueryParams params) {
294
		String queryWithDates = query;
295
		if (params.getStartFrom() != null) {
296
			queryWithDates += " AND startdate >= '" + params.getStartFrom() + "'";
297
		}
298
		if (params.getStartUntil() != null) {
299
			queryWithDates += " AND startdate <= '" + params.getStartUntil() + "'";
300
		}
301
		if (params.getEndFrom() != null) {
302
			queryWithDates += " AND enddate >= '" + params.getEndFrom() + "'";
303
		}
304
		if (params.getEndUntil() != null) {
305
			queryWithDates += " AND enddate <= '" + params.getEndUntil() + "'";
306
		}
307
		return queryWithDates;
308
	}
309

  
310
	public void setProjectsFundingQueryTemplate(final Resource projectsFundingQueryTemplate) {
311
		this.projectsFundingQueryTemplate = projectsFundingQueryTemplate;
312
	}
313

  
314
}
modules/dnet-openaire-projects-exporter/trunk/src/main/java/eu/dnetlib/DnetOpenaireProjectsExporterApplication.java
1
package eu.dnetlib;
2

  
3
import org.apache.commons.logging.Log;
4
import org.apache.commons.logging.LogFactory;
5
import org.springframework.boot.SpringApplication;
6
import org.springframework.boot.autoconfigure.SpringBootApplication;
7
import org.springframework.web.bind.annotation.RequestMapping;
8
import org.springframework.web.bind.annotation.RestController;
9

  
10
@RestController
11
@SpringBootApplication
12
public class DnetOpenaireProjectsExporterApplication {
13

  
14
	private static final Log log = LogFactory.getLog(DnetOpenaireProjectsExporterApplication.class);
15

  
16
	@RequestMapping("/")
... This diff was truncated because it exceeds the maximum size that can be displayed.

Also available in: Unified diff