1 |
57029
|
argiro.kok
|
package eu.dnetlib.data.claims.utils;
|
2 |
39985
|
eri.katsar
|
|
3 |
57029
|
argiro.kok
|
import eu.dnetlib.data.claims.entity.*;
|
4 |
40696
|
argiro.kok
|
|
5 |
40941
|
argiro.kok
|
import java.text.SimpleDateFormat;
|
6 |
48012
|
tsampikos.
|
import java.util.ArrayList;
|
7 |
40941
|
argiro.kok
|
import java.util.Date;
|
8 |
42199
|
argiro.kok
|
import java.util.List;
|
9 |
47219
|
argiro.kok
|
import org.apache.log4j.Logger;
|
10 |
39985
|
eri.katsar
|
|
11 |
40696
|
argiro.kok
|
|
12 |
39985
|
eri.katsar
|
public class QueryGenerator {
|
13 |
42199
|
argiro.kok
|
boolean updateOnInsert;
|
14 |
47039
|
argiro.kok
|
String migrationTable ="claims"; //a table or a view containing old schema claims - But only user claims, not those where agent='inference'
|
15 |
47219
|
argiro.kok
|
private static final Logger logger = Logger.getLogger(QueryGenerator.class);
|
16 |
42199
|
argiro.kok
|
|
17 |
|
|
public boolean isUpdateOnInsert() {
|
18 |
|
|
return updateOnInsert;
|
19 |
|
|
}
|
20 |
|
|
|
21 |
|
|
public void setUpdateOnInsert(boolean updateOnInsert) {
|
22 |
|
|
this.updateOnInsert = updateOnInsert;
|
23 |
|
|
}
|
24 |
|
|
|
25 |
|
|
/**
|
26 |
41621
|
argiro.kok
|
** Insert Queries **
|
27 |
|
|
**/
|
28 |
52857
|
argiro.kok
|
public String generateInsertQueryForClaimsOrphanIds(String id){
|
29 |
|
|
return "INSERT INTO claims_orphan_ids values (" + id + ");";
|
30 |
|
|
}
|
31 |
48012
|
tsampikos.
|
private String generateInsertResultQuery(String openaire_id, String result_type, String title, String collected_from , String external_url, String doi, String orcidworkid, String access_rights , String embargo_end_date, String best_license, String record_path, String record_format, ArrayList<Object> params) {
|
32 |
40696
|
argiro.kok
|
if(title!=null&&title.contains("'")){
|
33 |
|
|
title=title.replace("'","''");
|
34 |
|
|
}
|
35 |
41157
|
argiro.kok
|
String fields="openaire_id";
|
36 |
40941
|
argiro.kok
|
if(result_type!=null){
|
37 |
|
|
fields+=", result_type";
|
38 |
|
|
}
|
39 |
|
|
if(title!=null){
|
40 |
|
|
fields+=", title";
|
41 |
|
|
}
|
42 |
|
|
if(collected_from!=null){
|
43 |
|
|
fields+=", collected_from";
|
44 |
|
|
}
|
45 |
|
|
if(external_url!=null){
|
46 |
|
|
fields+=", external_url";
|
47 |
|
|
}
|
48 |
|
|
if(doi!=null){
|
49 |
|
|
fields+=", doi";
|
50 |
|
|
}
|
51 |
41621
|
argiro.kok
|
if(orcidworkid!=null){
|
52 |
|
|
fields+=", orcidworkid";
|
53 |
|
|
}
|
54 |
40941
|
argiro.kok
|
if(access_rights!=null){
|
55 |
|
|
fields+=", access_rights";
|
56 |
|
|
}
|
57 |
41157
|
argiro.kok
|
if(embargo_end_date!=null){
|
58 |
|
|
fields+=", embargo_end_date";
|
59 |
|
|
}
|
60 |
40941
|
argiro.kok
|
if(best_license!=null){
|
61 |
|
|
fields+=", best_license";
|
62 |
|
|
}
|
63 |
41408
|
argiro.kok
|
if(record_path!=null){
|
64 |
|
|
fields+=", record_path";
|
65 |
41157
|
argiro.kok
|
}
|
66 |
41408
|
argiro.kok
|
if(record_format!=null){
|
67 |
|
|
fields+=", record_format";
|
68 |
|
|
}
|
69 |
48012
|
tsampikos.
|
String values="?";
|
70 |
|
|
params.add(openaire_id);
|
71 |
40941
|
argiro.kok
|
if(result_type!=null){
|
72 |
48012
|
tsampikos.
|
values+=",?";
|
73 |
|
|
params.add(result_type);
|
74 |
40941
|
argiro.kok
|
}
|
75 |
|
|
if(title!=null){
|
76 |
48012
|
tsampikos.
|
values+=",?";
|
77 |
|
|
params.add(title);
|
78 |
40941
|
argiro.kok
|
}
|
79 |
|
|
if(collected_from!=null){
|
80 |
48012
|
tsampikos.
|
values+=",?";
|
81 |
|
|
params.add(collected_from);
|
82 |
40941
|
argiro.kok
|
}
|
83 |
|
|
if(external_url!=null){
|
84 |
48012
|
tsampikos.
|
values+=",?";
|
85 |
|
|
params.add(external_url);
|
86 |
40941
|
argiro.kok
|
}
|
87 |
|
|
if(doi!=null){
|
88 |
48012
|
tsampikos.
|
values+=",?";
|
89 |
|
|
params.add(doi);
|
90 |
40941
|
argiro.kok
|
}
|
91 |
41621
|
argiro.kok
|
if(orcidworkid!=null){
|
92 |
48012
|
tsampikos.
|
values+=",?";
|
93 |
|
|
params.add(orcidworkid);
|
94 |
41621
|
argiro.kok
|
}
|
95 |
40941
|
argiro.kok
|
if(access_rights!=null){
|
96 |
48012
|
tsampikos.
|
values+=",?";
|
97 |
|
|
params.add(access_rights);
|
98 |
40941
|
argiro.kok
|
}
|
99 |
41157
|
argiro.kok
|
if(embargo_end_date!=null){
|
100 |
48012
|
tsampikos.
|
values+=",?::date";
|
101 |
|
|
params.add(embargo_end_date);
|
102 |
41157
|
argiro.kok
|
}
|
103 |
40941
|
argiro.kok
|
if(best_license!=null){
|
104 |
48012
|
tsampikos.
|
values+=",?";
|
105 |
|
|
params.add(best_license);
|
106 |
40941
|
argiro.kok
|
}
|
107 |
41408
|
argiro.kok
|
if(record_path!=null){
|
108 |
48012
|
tsampikos.
|
values+=",?";
|
109 |
|
|
params.add(record_path);
|
110 |
41157
|
argiro.kok
|
}
|
111 |
41408
|
argiro.kok
|
if(record_format!=null){
|
112 |
48012
|
tsampikos.
|
values+=",?";
|
113 |
|
|
params.add(record_format);
|
114 |
41408
|
argiro.kok
|
}
|
115 |
48012
|
tsampikos.
|
params.add(openaire_id);
|
116 |
40941
|
argiro.kok
|
return "INSERT INTO result("+fields+")\n" +
|
117 |
|
|
" Select "+values+
|
118 |
48012
|
tsampikos.
|
" where not exists (select openaire_id from result where openaire_id=?)\n" +
|
119 |
40696
|
argiro.kok
|
" RETURNING openaire_id";
|
120 |
42199
|
argiro.kok
|
//TODO update on insert when exists
|
121 |
40696
|
argiro.kok
|
}
|
122 |
48012
|
tsampikos.
|
public String generateInsertProjectQuery(String openaire_id, String name, String acronym, String funder_id, String funder_name, String funder_acronym, List<String> contactEmails, ArrayList<Object> params) {
|
123 |
40941
|
argiro.kok
|
if(name!=null) {
|
124 |
|
|
name = name.replace("'", "''");
|
125 |
|
|
}
|
126 |
|
|
String fields="openaire_id";
|
127 |
|
|
if(name!=null){
|
128 |
|
|
fields+=",name";
|
129 |
|
|
}
|
130 |
|
|
if(acronym!=null){
|
131 |
|
|
fields+=",acronym";
|
132 |
|
|
}
|
133 |
|
|
if(funder_id!=null){
|
134 |
|
|
fields+=",funder_id";
|
135 |
|
|
}
|
136 |
|
|
if(funder_name!=null){
|
137 |
|
|
fields+=",funder_name";
|
138 |
|
|
}
|
139 |
41621
|
argiro.kok
|
if(funder_acronym!=null){
|
140 |
|
|
fields+=",funder_acronym";
|
141 |
|
|
}
|
142 |
46918
|
argiro.kok
|
if(contactEmails != null && contactEmails.size() > 0){
|
143 |
|
|
fields+=",contact_person";
|
144 |
|
|
}
|
145 |
48012
|
tsampikos.
|
String values="?";
|
146 |
|
|
params.add(openaire_id);
|
147 |
40941
|
argiro.kok
|
if(name!=null){
|
148 |
48012
|
tsampikos.
|
values+=",?";
|
149 |
|
|
params.add(name);
|
150 |
40941
|
argiro.kok
|
}
|
151 |
|
|
if(acronym!=null){
|
152 |
48012
|
tsampikos.
|
values+=",?";
|
153 |
|
|
params.add(acronym);
|
154 |
40941
|
argiro.kok
|
}
|
155 |
|
|
if(funder_id!=null){
|
156 |
48012
|
tsampikos.
|
values+=",?";
|
157 |
|
|
params.add(funder_id);
|
158 |
40941
|
argiro.kok
|
}
|
159 |
|
|
if(funder_name!=null){
|
160 |
48012
|
tsampikos.
|
values+=",?";
|
161 |
|
|
params.add(funder_name);
|
162 |
40941
|
argiro.kok
|
}
|
163 |
41621
|
argiro.kok
|
if(funder_acronym!=null){
|
164 |
48012
|
tsampikos.
|
values+=",?";
|
165 |
|
|
params.add(funder_acronym);
|
166 |
41621
|
argiro.kok
|
}
|
167 |
46918
|
argiro.kok
|
if(contactEmails != null && contactEmails.size() > 0){
|
168 |
48012
|
tsampikos.
|
values+=",string_to_array(?, ',')";
|
169 |
|
|
//String email_json = "{";
|
170 |
|
|
String email_json = "";
|
171 |
46918
|
argiro.kok
|
for(int i = 0; i< contactEmails.size(); i++){
|
172 |
48012
|
tsampikos.
|
email_json+=contactEmails.get(i)+((i < contactEmails.size() -1 )?",":"");
|
173 |
|
|
//params.add(contactEmails.get(i));
|
174 |
46918
|
argiro.kok
|
}
|
175 |
48012
|
tsampikos.
|
//email_json+="";
|
176 |
|
|
params.add(email_json);
|
177 |
46918
|
argiro.kok
|
}
|
178 |
48012
|
tsampikos.
|
params.add(openaire_id);
|
179 |
40941
|
argiro.kok
|
return "INSERT INTO project("+fields+")\n" +
|
180 |
|
|
" Select "+values +
|
181 |
48012
|
tsampikos.
|
" where not exists (select openaire_id from project where openaire_id=?)\n" +
|
182 |
40696
|
argiro.kok
|
" RETURNING openaire_id";
|
183 |
42199
|
argiro.kok
|
//TODO update on insert when exists
|
184 |
40696
|
argiro.kok
|
}
|
185 |
48012
|
tsampikos.
|
private String generateInsertContextQuery(String openaire_id, String name, ArrayList<Object> params) {
|
186 |
40941
|
argiro.kok
|
String fields="openaire_id";
|
187 |
|
|
if(name!=null){
|
188 |
|
|
fields+=",name";
|
189 |
|
|
}
|
190 |
48012
|
tsampikos.
|
String values="?";
|
191 |
|
|
params.add(openaire_id);
|
192 |
40941
|
argiro.kok
|
if(name!=null){
|
193 |
48012
|
tsampikos.
|
values+=",?";
|
194 |
|
|
params.add(name);
|
195 |
40941
|
argiro.kok
|
}
|
196 |
48012
|
tsampikos.
|
params.add(openaire_id);
|
197 |
40941
|
argiro.kok
|
return "INSERT INTO context("+fields+")\n" +
|
198 |
|
|
" Select "+values+
|
199 |
48012
|
tsampikos.
|
" where not exists (select openaire_id from context where openaire_id=?)\n" +
|
200 |
40696
|
argiro.kok
|
" RETURNING openaire_id";
|
201 |
42199
|
argiro.kok
|
//TODO update on insert when exists
|
202 |
40696
|
argiro.kok
|
}
|
203 |
48012
|
tsampikos.
|
private String generateInsertClaimQuery(Date date, String claimedBy, String source_type, String target_type , String source_id , String target_id, String semantics, ArrayList<Object> params) {
|
204 |
40696
|
argiro.kok
|
|
205 |
42278
|
argiro.kok
|
SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
|
206 |
40941
|
argiro.kok
|
String dateStr=null;
|
207 |
|
|
dateStr=(format.format(date));
|
208 |
41791
|
argiro.kok
|
String fields="claim_date,claimedBy,source_type,target_type, source_id, target_id";
|
209 |
|
|
if(semantics!=null){
|
210 |
|
|
fields+=",semantics";
|
211 |
|
|
}
|
212 |
48012
|
tsampikos.
|
String values="?::timestamp,?,?,?,?,?";
|
213 |
|
|
params.add(dateStr);
|
214 |
|
|
params.add(claimedBy);
|
215 |
|
|
params.add(source_type);
|
216 |
|
|
params.add(target_type);
|
217 |
|
|
params.add(source_id);
|
218 |
|
|
params.add(target_id);
|
219 |
41791
|
argiro.kok
|
if(semantics!=null){
|
220 |
48012
|
tsampikos.
|
values+=",?";
|
221 |
|
|
params.add(semantics);
|
222 |
41791
|
argiro.kok
|
}
|
223 |
40941
|
argiro.kok
|
|
224 |
41791
|
argiro.kok
|
return " INSERT INTO claim( "+fields+")\n" +
|
225 |
|
|
" VALUES ( "+values+")\n" +
|
226 |
40773
|
argiro.kok
|
" RETURNING id, source_id, target_id\n";
|
227 |
40696
|
argiro.kok
|
}
|
228 |
41621
|
argiro.kok
|
|
229 |
48012
|
tsampikos.
|
private String generateInsertFullClaimQuery(String targetQuery, String sourceQuery, Claim claim, ArrayList<Object> params) {
|
230 |
41621
|
argiro.kok
|
if (claim ==null || targetQuery ==null || sourceQuery == null){
|
231 |
|
|
return null;
|
232 |
|
|
}
|
233 |
42199
|
argiro.kok
|
String sourceTableName= getSourceTableName(claim.getSourceType());
|
234 |
|
|
String targetTableName= getTargetTableName(claim.getTargetType());
|
235 |
41621
|
argiro.kok
|
if (sourceTableName ==null || targetTableName ==null ){
|
236 |
|
|
return null;
|
237 |
|
|
}
|
238 |
|
|
return " WITH target AS (\n" +
|
239 |
|
|
targetQuery+
|
240 |
|
|
" ),\n source AS (\n" +
|
241 |
|
|
sourceQuery+
|
242 |
|
|
" ),\n myclaim AS (\n" +
|
243 |
48012
|
tsampikos.
|
generateInsertClaimQuery(claim.getDate(), claim.getUserMail(), claim.getSourceType(), claim.getTargetType(), claim.getSource().getOpenaireId(), claim.getTarget().getOpenaireId(),this.createSemanticsType(claim.getSourceType(),claim.getTargetType()), params) +
|
244 |
41621
|
argiro.kok
|
" ),\n ins4 AS (\n" +
|
245 |
|
|
" INSERT INTO "+sourceTableName+" (claim_id, openaire_id)\n" +
|
246 |
|
|
" SELECT id, source_id\n" +
|
247 |
|
|
" FROM myclaim) \n"+
|
248 |
|
|
" INSERT INTO "+targetTableName+"(claim_id, openaire_id)\n" +
|
249 |
|
|
" SELECT id, target_id\n" +
|
250 |
42271
|
argiro.kok
|
" FROM myclaim "+
|
251 |
|
|
" RETURNING claim_id;";
|
252 |
41621
|
argiro.kok
|
}
|
253 |
48012
|
tsampikos.
|
public String generateInsertFullClaimQuery( Claim claim, ArrayList<Object> params) {
|
254 |
41621
|
argiro.kok
|
String targetQuery =null;
|
255 |
|
|
String sourceQuery=null;
|
256 |
48012
|
tsampikos.
|
targetQuery=generateInsertEntityQuery(claim.getTarget(),claim.getTargetType(), params);
|
257 |
|
|
sourceQuery=generateInsertEntityQuery(claim.getSource(),claim.getSourceType(), params);
|
258 |
|
|
return generateInsertFullClaimQuery(targetQuery,sourceQuery,claim, params);
|
259 |
41621
|
argiro.kok
|
|
260 |
|
|
}
|
261 |
48012
|
tsampikos.
|
private String generateInsertEntityQuery(OpenaireEntity openaireEntity, String type, ArrayList<Object> params){
|
262 |
41791
|
argiro.kok
|
String query=null;
|
263 |
41621
|
argiro.kok
|
if(type==null){
|
264 |
|
|
return null;
|
265 |
|
|
}
|
266 |
52857
|
argiro.kok
|
if(type.equals(ClaimUtils.DATASET)||type.equals(ClaimUtils.PUBLICATION)||type.equals(ClaimUtils.SOFTWARE)||type.equals(ClaimUtils.OTHER)){
|
267 |
41621
|
argiro.kok
|
Result result=(Result)openaireEntity;
|
268 |
48012
|
tsampikos.
|
query=generateInsertResultQuery(result.getOpenaireId(),result.getResultType(),result.getTitle(),result.getCollectedFrom(),result.getExternalUrl(),result.getDoi(), result.getOrcidworkid(), result.getAccessRights(),result.getEmbargoEndDate(),result.getBestLicense(),result.getRecordPath(), result.getRecordFormat(), params);
|
269 |
41621
|
argiro.kok
|
}else if(type.equals(ClaimUtils.PROJECT)){
|
270 |
|
|
Project project=(Project)openaireEntity;
|
271 |
48012
|
tsampikos.
|
query=generateInsertProjectQuery(project.getOpenaireId(),project.getName(),project.getAcronym(),project.getFunderId(),project.getFunderName(),project.getFunderShortName(),project.getContactEmails(), params);
|
272 |
41791
|
argiro.kok
|
}else if(type.equals(ClaimUtils.CONTEXT)){
|
273 |
41621
|
argiro.kok
|
Context context= (Context)openaireEntity;
|
274 |
48012
|
tsampikos.
|
query=generateInsertContextQuery(context.getOpenaireId(),context.getTitle(), params);
|
275 |
41621
|
argiro.kok
|
}
|
276 |
|
|
|
277 |
|
|
return query;
|
278 |
|
|
}
|
279 |
41793
|
argiro.kok
|
|
280 |
42199
|
argiro.kok
|
private String getSourceTableName(String type){
|
281 |
|
|
String tablename="has_source_";
|
282 |
|
|
if(type==null){
|
283 |
|
|
return null;
|
284 |
|
|
}else{
|
285 |
|
|
tablename+=getTypeForTable(type);
|
286 |
|
|
}
|
287 |
|
|
return tablename;
|
288 |
|
|
}
|
289 |
|
|
private String getTargetTableName(String type){
|
290 |
41621
|
argiro.kok
|
String tablename="has_target_";
|
291 |
|
|
if(type==null){
|
292 |
|
|
return null;
|
293 |
42199
|
argiro.kok
|
}else{
|
294 |
|
|
tablename+=getTypeForTable(type);
|
295 |
41621
|
argiro.kok
|
}
|
296 |
|
|
return tablename;
|
297 |
|
|
}
|
298 |
48012
|
tsampikos.
|
public String generateInsertClaimResultQuery(String targetQuery, String sourceQuery, Claim claim, ArrayList<Object> params) {
|
299 |
40696
|
argiro.kok
|
|
300 |
|
|
return " WITH ins1 AS (\n" +
|
301 |
41350
|
argiro.kok
|
targetQuery+
|
302 |
40773
|
argiro.kok
|
" ),\n ins2 AS (\n" +
|
303 |
41350
|
argiro.kok
|
sourceQuery+
|
304 |
40773
|
argiro.kok
|
" ),\n ins3 AS (\n" +
|
305 |
48012
|
tsampikos.
|
generateInsertClaimQuery(claim.getDate(), claim.getUserMail(), claim.getSourceType(), claim.getTargetType(), claim.getSource().getOpenaireId(), claim.getTarget().getOpenaireId(),this.createSemanticsType(claim.getSourceType(),claim.getTargetType()), params) +
|
306 |
40773
|
argiro.kok
|
" ),\n ins4 AS (\n" +
|
307 |
41621
|
argiro.kok
|
" INSERT INTO has_source_result (claim_id, openaire_id)\n" +
|
308 |
|
|
" SELECT id, source_id\n" +
|
309 |
40773
|
argiro.kok
|
" FROM ins3) \n"+
|
310 |
|
|
" INSERT INTO has_target_result(claim_id, openaire_id)\n" +
|
311 |
|
|
" SELECT id, target_id\n" +
|
312 |
40696
|
argiro.kok
|
" FROM ins3;";
|
313 |
|
|
}
|
314 |
41411
|
argiro.kok
|
|
315 |
|
|
/**
|
316 |
|
|
*
|
317 |
|
|
* @param sourceType
|
318 |
|
|
* @param targetType
|
319 |
|
|
* @return "resultResult_"+targetType+sourceType+"_isRelatedTo"
|
320 |
|
|
*/
|
321 |
41408
|
argiro.kok
|
private String createSemanticsType(String sourceType,String targetType){
|
322 |
|
|
String semantics=null;
|
323 |
49865
|
argiro.kok
|
// if((targetType.equals(ClaimUtils.PUBLICATION))&&(sourceType.equals(ClaimUtils.DATASET))){
|
324 |
|
|
// semantics="resultResult_relationship_isRelatedTo";
|
325 |
|
|
// }
|
326 |
|
|
// else if(targetType.equals(ClaimUtils.DATASET)&&sourceType.equals(ClaimUtils.PUBLICATION)){
|
327 |
|
|
// semantics="resultResult_relationship_isRelatedTo";
|
328 |
|
|
// }
|
329 |
|
|
// if((targetType.equals(ClaimUtils.PUBLICATION))&&(sourceType.equals(ClaimUtils.SOFTWARE))){
|
330 |
|
|
// semantics="resultResult_relationship_isRelatedTo";
|
331 |
|
|
// }
|
332 |
|
|
// else if(targetType.equals(ClaimUtils.SOFTWARE)&&sourceType.equals(ClaimUtils.PUBLICATION)){
|
333 |
|
|
// semantics="resultResult_relationship_isRelatedTo";
|
334 |
|
|
// }
|
335 |
|
|
// if((targetType.equals(ClaimUtils.DATASET))&&(sourceType.equals(ClaimUtils.SOFTWARE))){
|
336 |
|
|
// semantics="resultResult_relationship_isRelatedTo";
|
337 |
|
|
// }
|
338 |
|
|
// else if(targetType.equals(ClaimUtils.SOFTWARE)&&sourceType.equals(ClaimUtils.DATASET)){
|
339 |
|
|
// semantics="resultResult_relationship_isRelatedTo";
|
340 |
|
|
// }
|
341 |
|
|
// else if((targetType.equals(ClaimUtils.PUBLICATION))&&(sourceType.equals(ClaimUtils.PUBLICATION))){
|
342 |
|
|
// semantics="resultResult_relationship_isRelatedTo";
|
343 |
|
|
// }
|
344 |
|
|
// else if(targetType.equals(ClaimUtils.DATASET)&&sourceType.equals(ClaimUtils.DATASET)){
|
345 |
|
|
// semantics="resultResult_relationship_isRelatedTo";
|
346 |
|
|
// }
|
347 |
|
|
// else if(targetType.equals(ClaimUtils.SOFTWARE)&&sourceType.equals(ClaimUtils.SOFTWARE)){
|
348 |
|
|
// semantics="resultResult_supplement_isSupplementTo";
|
349 |
|
|
// }
|
350 |
|
|
//add above more specific semantics for pub,data and software
|
351 |
52857
|
argiro.kok
|
if((targetType.equals(ClaimUtils.PUBLICATION)||targetType.equals(ClaimUtils.DATASET)||targetType.equals(ClaimUtils.SOFTWARE)||targetType.equals(ClaimUtils.OTHER))&&(sourceType.equals(ClaimUtils.PUBLICATION)||sourceType.equals(ClaimUtils.DATASET)||sourceType.equals(ClaimUtils.SOFTWARE)||sourceType.equals(ClaimUtils.OTHER))){
|
352 |
49865
|
argiro.kok
|
semantics="resultResult_relationship_isRelatedTo";
|
353 |
41791
|
argiro.kok
|
}
|
354 |
52857
|
argiro.kok
|
else if((targetType.equals(ClaimUtils.PUBLICATION)||targetType.equals(ClaimUtils.DATASET)||targetType.equals(ClaimUtils.SOFTWARE)||targetType.equals(ClaimUtils.OTHER))&&(sourceType.equals(ClaimUtils.PROJECT))){
|
355 |
47505
|
argiro.kok
|
semantics= "resultProject_outcome_produces"; //"resultProject_outcome_isProducedBy";
|
356 |
41791
|
argiro.kok
|
}
|
357 |
52857
|
argiro.kok
|
else if((targetType.equals(ClaimUtils.PUBLICATION)||targetType.equals(ClaimUtils.DATASET)||targetType.equals(ClaimUtils.SOFTWARE)||targetType.equals(ClaimUtils.OTHER))&&(sourceType.equals(ClaimUtils.CONTEXT))){
|
358 |
47505
|
argiro.kok
|
semantics= "isRelevantTo";
|
359 |
|
|
}
|
360 |
41408
|
argiro.kok
|
|
361 |
|
|
return semantics;
|
362 |
|
|
}
|
363 |
41791
|
argiro.kok
|
/* public String generateInsertClaimResultQuery(Result targetResult, Result sourceResult, Claim claim) {
|
364 |
40696
|
argiro.kok
|
|
365 |
47219
|
argiro.kok
|
return generateInsertClaimResultQuery(generateInsertResultQuery(targetResult.getOpenaireId(), targetResult.getResultType(), targetResult.getTitle(), targetResult.getCollectedFrom(), targetResult.getExternalUrl(), targetResult.getDOI(),targetResult.getOrcidworkid(), targetResult.getAccessRights(), targetResult.getEmbargoEndDate(), targetResult.getBestLicense(), targetResult.getRecordPath(), targetResult.getRecordFormat()),
|
366 |
|
|
generateInsertResultQuery(sourceResult.getOpenaireId(), sourceResult.getResultType(), sourceResult.getTitle(), sourceResult.getCollectedFrom(), sourceResult.getExternalUrl(), sourceResult.getDOI(), sourceResult.getOrcidworkid(), sourceResult.getAccessRights(), targetResult.getEmbargoEndDate(), sourceResult.getBestLicense(), sourceResult.getRecordPath(),sourceResult.getRecordFormat()),claim);
|
367 |
41350
|
argiro.kok
|
}
|
368 |
|
|
public String generateInsertClaimProjectQuery(String targetQuery, String projectQuery, Claim claim) {
|
369 |
|
|
|
370 |
40696
|
argiro.kok
|
return " WITH ins1 AS (\n" +
|
371 |
41350
|
argiro.kok
|
targetQuery+
|
372 |
40773
|
argiro.kok
|
" ),\n ins2 AS (\n" +
|
373 |
41350
|
argiro.kok
|
projectQuery+
|
374 |
40773
|
argiro.kok
|
" ),\n ins3 AS (\n" +
|
375 |
41621
|
argiro.kok
|
generateInsertClaimQuery(claim.getDate(), claim.getUserMail(), claim.getSourceType(), claim.getTargetType(), claim.getSource().getOpenaireId(), claim.getTarget().getOpenaireId(),"resultProject_outcome_isProducedBy") +
|
376 |
40773
|
argiro.kok
|
" ),\n ins4 AS (\n" +
|
377 |
41621
|
argiro.kok
|
" INSERT INTO has_source_project (claim_id, openaire_id)\n" +
|
378 |
|
|
" SELECT id, source_id\n" +
|
379 |
40773
|
argiro.kok
|
" FROM ins3) \n"+
|
380 |
|
|
" INSERT INTO has_target_result(claim_id, openaire_id)\n" +
|
381 |
|
|
" SELECT id, target_id\n" +
|
382 |
40696
|
argiro.kok
|
" FROM ins3;";
|
383 |
|
|
}
|
384 |
41350
|
argiro.kok
|
public String generateInsertClaimProjectQuery(Result targetResult, Project project, Claim claim) {
|
385 |
|
|
|
386 |
|
|
return
|
387 |
47219
|
argiro.kok
|
generateInsertClaimProjectQuery(generateInsertResultQuery(targetResult.getOpenaireId(), targetResult.getResultType(), targetResult.getTitle(), targetResult.getCollectedFrom(), targetResult.getExternalUrl(), targetResult.getDOI(), targetResult.getOrcidworkid(), targetResult.getAccessRights(), targetResult.getEmbargoEndDate(), targetResult.getBestLicense(), targetResult.getRecordPath(),targetResult.getRecordFormat()),
|
388 |
41621
|
argiro.kok
|
generateInsertProjectQuery(project.getOpenaireId(), project.getName(), project.getAcronym(), project.getFunderId(), project.getFunderName(), project.getFunderShortName()), claim);
|
389 |
41350
|
argiro.kok
|
}
|
390 |
41161
|
katerina.i
|
public String generateInsertClaimContextQuery(Result targetResult, Context context, Claim claim) {
|
391 |
40696
|
argiro.kok
|
|
392 |
41350
|
argiro.kok
|
return generateInsertClaimContextQuery
|
393 |
47219
|
argiro.kok
|
(generateInsertResultQuery(targetResult.getOpenaireId(), targetResult.getResultType(), targetResult.getTitle(), targetResult.getCollectedFrom(), targetResult.getExternalUrl(), targetResult.getDOI(), targetResult.getOrcidworkid(), targetResult.getAccessRights(), targetResult.getEmbargoEndDate(), targetResult.getBestLicense(), targetResult.getRecordPath(),targetResult.getRecordFormat())
|
394 |
41350
|
argiro.kok
|
,generateInsertContextQuery(context.getOpenaireId(), context.getTitle()),claim);
|
395 |
|
|
}
|
396 |
|
|
public String generateInsertClaimContextQuery(String targetQuery,String sourceQuery, Claim claim) {
|
397 |
|
|
|
398 |
|
|
return " WITH ins1 AS (\n" +targetQuery+
|
399 |
40773
|
argiro.kok
|
" ),\n ins2 AS (\n" +
|
400 |
41350
|
argiro.kok
|
sourceQuery+
|
401 |
40773
|
argiro.kok
|
" ),\n ins3 AS (\n" +
|
402 |
41621
|
argiro.kok
|
generateInsertClaimQuery(claim.getDate(), claim.getUserMail(), claim.getSourceType(), claim.getTargetType(), claim.getSource().getOpenaireId(), claim.getTarget().getOpenaireId(),"") +
|
403 |
40773
|
argiro.kok
|
" ), ins4 AS (\n" +
|
404 |
40696
|
argiro.kok
|
" INSERT INTO has_source_context(claim_id, openaire_id)\n" +
|
405 |
|
|
" SELECT id, source_id\n" +
|
406 |
40773
|
argiro.kok
|
" FROM ins3) \n"+
|
407 |
|
|
" INSERT INTO has_target_result(claim_id, openaire_id)\n" +
|
408 |
|
|
" SELECT id, target_id\n" +
|
409 |
40696
|
argiro.kok
|
" FROM ins3;";
|
410 |
|
|
}
|
411 |
41791
|
argiro.kok
|
*/
|
412 |
41621
|
argiro.kok
|
/**
|
413 |
|
|
** Delete Tables Queries **
|
414 |
|
|
**/
|
415 |
|
|
|
416 |
41161
|
katerina.i
|
public String generateDeleteClaimTablesQuery() {
|
417 |
40773
|
argiro.kok
|
return "drop table if exists has_source_context;\n" +
|
418 |
|
|
"drop table if exists has_source_project;\n" +
|
419 |
|
|
"drop table if exists has_source_result;\n" +
|
420 |
|
|
"drop table if exists has_target_project;\n" +
|
421 |
|
|
"drop table if exists has_target_result;\n" +
|
422 |
|
|
"drop table if exists claim;\n" +
|
423 |
|
|
"drop table if exists project;\n" +
|
424 |
|
|
"drop table if exists result;\n" +
|
425 |
|
|
"drop table if exists context;\n" +
|
426 |
52857
|
argiro.kok
|
"drop table if exists claims_orphan_ids;\n" +
|
427 |
40696
|
argiro.kok
|
"\n ";
|
428 |
|
|
|
429 |
|
|
}
|
430 |
41621
|
argiro.kok
|
|
431 |
|
|
/**
|
432 |
|
|
** Create Tables Queries **
|
433 |
|
|
**/
|
434 |
|
|
|
435 |
41161
|
katerina.i
|
public String generateCreateClaimTablesQuery() {
|
436 |
40696
|
argiro.kok
|
return "CREATE TABLE project (\n" +
|
437 |
|
|
" openaire_id varchar(60) primary key NOT NULL,\n" +
|
438 |
41408
|
argiro.kok
|
" name text NOT NULL,\n" +
|
439 |
47008
|
argiro.kok
|
" acronym text,\n" +
|
440 |
41408
|
argiro.kok
|
" funder_id varchar(60) NOT NULL,\n" +
|
441 |
47008
|
argiro.kok
|
" funder_name text NOT NULL,\n" +
|
442 |
|
|
" funder_acronym text NOT NULL,\n" +
|
443 |
46977
|
argiro.kok
|
" notify boolean DEFAULT TRUE,\n" +
|
444 |
47039
|
argiro.kok
|
" contact_person text[],\n" +
|
445 |
46918
|
argiro.kok
|
" token varchar(60)\n" +
|
446 |
40696
|
argiro.kok
|
");\n" +
|
447 |
|
|
"\n" +
|
448 |
|
|
"CREATE TABLE result (\n" +
|
449 |
41408
|
argiro.kok
|
" id serial NOT NULL,\n" +
|
450 |
|
|
" openaire_id varchar(60) primary key NOT NULL,\n" +
|
451 |
40696
|
argiro.kok
|
"\t\tresult_type varchar(30) NOT NULL,\n" +
|
452 |
41157
|
argiro.kok
|
"\t\tdoi text,\n" +
|
453 |
41621
|
argiro.kok
|
"\t\torcidworkid text,\n" +
|
454 |
40941
|
argiro.kok
|
"\t\ttitle text,\n" +
|
455 |
40696
|
argiro.kok
|
"\t\taccess_rights varchar(30),\n" +
|
456 |
41157
|
argiro.kok
|
"\t\tembargo_end_date date,\n" +
|
457 |
40696
|
argiro.kok
|
"\t\tbest_license varchar(30),\t\t\n" +
|
458 |
40941
|
argiro.kok
|
"\t\texternal_url text,\n" +
|
459 |
|
|
"\t\tcollected_from varchar(60) ,\n" +
|
460 |
41408
|
argiro.kok
|
"\t\trecord_path text , \n" +
|
461 |
|
|
"\t\trecord_format varchar(10)\n" +
|
462 |
40696
|
argiro.kok
|
");\n" +
|
463 |
|
|
"\n" +
|
464 |
|
|
"CREATE TABLE context (\n" +
|
465 |
|
|
" openaire_id varchar(60) primary key,\n" +
|
466 |
40941
|
argiro.kok
|
" name text\n" +
|
467 |
40696
|
argiro.kok
|
");\n" +
|
468 |
|
|
"CREATE TABLE claim (\n" +
|
469 |
41408
|
argiro.kok
|
" id serial primary key NOT NULL,\n" +
|
470 |
41003
|
argiro.kok
|
" claim_date timestamp without time zone NOT NULL,\n" +
|
471 |
47008
|
argiro.kok
|
" claimedBy text NOT NULL,\n" +
|
472 |
40696
|
argiro.kok
|
" source_type varchar(30) NOT NULL,\n" +
|
473 |
|
|
" target_type varchar(30) NOT NULL,\n" +
|
474 |
|
|
" source_id varchar(60) NOT NULL,\n" +
|
475 |
|
|
" target_id varchar(60) references result(openaire_id) NOT NULL,\n" +
|
476 |
46918
|
argiro.kok
|
" curation_date timestamp without time zone,\n" +
|
477 |
47008
|
argiro.kok
|
" curated_by text,\n" +
|
478 |
46918
|
argiro.kok
|
" approved boolean DEFAULT TRUE,\n" +
|
479 |
41621
|
argiro.kok
|
" claim_status varchar(30),\n" +
|
480 |
48294
|
argiro.kok
|
" semantics varchar(60) NOT NULL\n" +
|
481 |
40696
|
argiro.kok
|
");\n" +
|
482 |
|
|
"\n" +
|
483 |
|
|
"CREATE TABLE has_source_context(\n" +
|
484 |
|
|
"\tclaim_id int references claim(id) NOT NULL,\n" +
|
485 |
41621
|
argiro.kok
|
"\topenaire_id varchar(60) references context(openaire_id) NOT NULL\n" +
|
486 |
40696
|
argiro.kok
|
");\n" +
|
487 |
|
|
"CREATE TABLE has_source_project(\n" +
|
488 |
|
|
"\tclaim_id int references claim(id) NOT NULL,\n" +
|
489 |
41621
|
argiro.kok
|
"\topenaire_id varchar(60) references project(openaire_id) NOT NULL\n" +
|
490 |
40696
|
argiro.kok
|
");\n" +
|
491 |
|
|
"CREATE TABLE has_source_result(\n" +
|
492 |
|
|
"\tclaim_id int references claim(id) NOT NULL,\n" +
|
493 |
41621
|
argiro.kok
|
"\topenaire_id varchar(60) references result(openaire_id) NOT NULL\n" +
|
494 |
40773
|
argiro.kok
|
");\n"+
|
495 |
|
|
"CREATE TABLE has_target_project(\n" +
|
496 |
|
|
"\tclaim_id int references claim(id) NOT NULL,\n" +
|
497 |
41621
|
argiro.kok
|
"\topenaire_id varchar(60) references project(openaire_id) NOT NULL\n" +
|
498 |
40773
|
argiro.kok
|
");\n" +
|
499 |
|
|
"CREATE TABLE has_target_result(\n" +
|
500 |
|
|
"\tclaim_id int references claim(id) NOT NULL,\n" +
|
501 |
41621
|
argiro.kok
|
"\topenaire_id varchar(60) references result(openaire_id) NOT NULL\n" +
|
502 |
52857
|
argiro.kok
|
");\n"+
|
503 |
|
|
"CREATE TABLE claims_orphan_ids (\n" +
|
504 |
|
|
" id integer "+
|
505 |
40773
|
argiro.kok
|
");\n"
|
506 |
40696
|
argiro.kok
|
|
507 |
40773
|
argiro.kok
|
;
|
508 |
|
|
|
509 |
40696
|
argiro.kok
|
}
|
510 |
41408
|
argiro.kok
|
|
511 |
41621
|
argiro.kok
|
/**
|
512 |
|
|
** Select Queries *Old* Database **
|
513 |
|
|
**/
|
514 |
39985
|
eri.katsar
|
|
515 |
41621
|
argiro.kok
|
|
516 |
41162
|
argiro.kok
|
public String generateSelectDMFByIdQuery(String id) {
|
517 |
47039
|
argiro.kok
|
return " Select * from "+this.migrationTable+" where type='dmf2actions' and xml NOT ILIKE '%<oaf:concept%' and resultid='"+id+"' order by date desc limit 1";
|
518 |
40773
|
argiro.kok
|
}
|
519 |
41791
|
argiro.kok
|
|
520 |
41162
|
argiro.kok
|
public String generateSelectConceptDMFClaimsQuery(Integer limit) {
|
521 |
47039
|
argiro.kok
|
return " Select * from "+this.migrationTable+" where ( type='dmf2actions' or type='updates2actions' ) and xml LIKE '%<oaf:concept%' "+(limit>0?" limit "+limit:"");
|
522 |
40086
|
argiro.kok
|
}
|
523 |
41220
|
argiro.kok
|
/**
|
524 |
|
|
*
|
525 |
|
|
* @param limit : for limit > 0 the query adds limit clause, for limit < 0 query skips limit clause
|
526 |
|
|
* @return
|
527 |
|
|
*/
|
528 |
41218
|
argiro.kok
|
public String generateSelectRelationEnrichedWithDMFClaimsQuery(Integer limit) {
|
529 |
47039
|
argiro.kok
|
return " select rel.*, dmf.dmf from (Select * from "+this.migrationTable+" where type='rels2actions' order by id) as rel left outer join \n" +
|
530 |
47505
|
argiro.kok
|
"(Select DISTINCT ON (resultid) resultid,date, xml as dmf from "+this.migrationTable+" where type='dmf2actions' and xml NOT ILIKE '%<oaf:concept%' order by resultid,date desc , xml) as dmf on rel.resultid=dmf.resultid "+
|
531 |
|
|
" order by rel.id "
|
532 |
41218
|
argiro.kok
|
+(limit>0?" limit "+limit:"");
|
533 |
|
|
|
534 |
|
|
}
|
535 |
41350
|
argiro.kok
|
|
536 |
|
|
/**
|
537 |
|
|
*
|
538 |
|
|
* @param limit : for limit > 0 the query adds limit clause, for limit < 0 query skips limit clause
|
539 |
|
|
* @return
|
540 |
|
|
*/
|
541 |
|
|
public String generateSelectConceptClaimsEnrichedWithDMFClaimsQuery(Integer limit) {
|
542 |
47039
|
argiro.kok
|
return " select rel.*, dmf.dmf from (Select * from "+this.migrationTable+" where ( type='dmf2actions' or type='updates2actions' ) and xml LIKE '%<oaf:concept%' order by id) as rel left outer join \n" +
|
543 |
|
|
"(Select DISTINCT ON (resultid) resultid,date, xml as dmf from "+this.migrationTable+" where type='dmf2actions' and xml NOT ILIKE '%<oaf:concept%' order by resultid,date desc , xml) as dmf on rel.resultid=dmf.resultid"
|
544 |
47505
|
argiro.kok
|
+" order by rel.id "
|
545 |
41350
|
argiro.kok
|
+(limit>0?" limit "+limit:"");
|
546 |
|
|
|
547 |
|
|
}
|
548 |
47039
|
argiro.kok
|
|
549 |
41444
|
argiro.kok
|
public String generateSelectConceptClaimByIDEnrichedWithDMFClaimsQuery(Integer limit, String claimID) {
|
550 |
47039
|
argiro.kok
|
return " select rel.*, dmf.dmf from (Select * from "+this.migrationTable+" where id='"+claimID+"' and ( type='dmf2actions' or type='updates2actions' ) and xml LIKE '%<oaf:concept%') as rel left outer join \n" +
|
551 |
|
|
"(Select DISTINCT ON (resultid) resultid,date, xml as dmf from "+this.migrationTable+" where type='dmf2actions' and xml NOT ILIKE '%<oaf:concept%' order by resultid,date desc , xml) as dmf on rel.resultid=dmf.resultid"
|
552 |
41444
|
argiro.kok
|
+(limit>0?" limit "+limit:"");
|
553 |
41350
|
argiro.kok
|
}
|
554 |
41444
|
argiro.kok
|
public String generateSelectRelationClaimByIDEnrichedWithDMFClaimsQuery(Integer limit, String claimID) {
|
555 |
47039
|
argiro.kok
|
return " select rel.*, dmf.dmf from (Select * from "+this.migrationTable+" where id='"+claimID+"' and type='rels2actions' ) as rel left outer join \n" +
|
556 |
|
|
"(Select DISTINCT ON (resultid) resultid,date, xml as dmf from "+this.migrationTable+" where type='dmf2actions' and xml NOT ILIKE '%<oaf:concept%' order by resultid,date desc , xml) as dmf on rel.resultid=dmf.resultid"
|
557 |
41444
|
argiro.kok
|
+(limit>0?" limit "+limit:"");
|
558 |
|
|
}
|
559 |
41003
|
argiro.kok
|
|
560 |
40086
|
argiro.kok
|
|
561 |
42199
|
argiro.kok
|
public String generateSelectConceptClaimByIdQuery(String id) {
|
562 |
47039
|
argiro.kok
|
return " Select * from "+this.migrationTable+" where ( type='dmf2actions' or type='updates2actions' ) and xml LIKE '%<oaf:concept%' and id='"+id+"'";
|
563 |
41196
|
argiro.kok
|
|
564 |
|
|
}
|
565 |
42199
|
argiro.kok
|
public String generateSelectRelationClaimByIdQuery(String id) {
|
566 |
47039
|
argiro.kok
|
return " Select * from "+this.migrationTable+" where type='rels2actions' and id='"+id+"'";
|
567 |
41196
|
argiro.kok
|
|
568 |
|
|
}
|
569 |
39985
|
eri.katsar
|
|
570 |
41791
|
argiro.kok
|
|
571 |
41621
|
argiro.kok
|
/**
|
572 |
|
|
** Select - Fetch Queries *New* Database **
|
573 |
|
|
**/
|
574 |
41791
|
argiro.kok
|
|
575 |
42762
|
argiro.kok
|
|
576 |
|
|
private String addPagingClause(Integer limit, Integer offset) {
|
577 |
|
|
if(limit==null && offset==null){
|
578 |
|
|
return "";
|
579 |
42214
|
argiro.kok
|
}
|
580 |
42762
|
argiro.kok
|
else if((limit==null || offset==null || offset <0 || limit<=0)){
|
581 |
|
|
limit=10;
|
582 |
|
|
offset=0;
|
583 |
|
|
}
|
584 |
48012
|
tsampikos.
|
return " limit " + limit + " offset " + offset;
|
585 |
42762
|
argiro.kok
|
|
586 |
42214
|
argiro.kok
|
}
|
587 |
42762
|
argiro.kok
|
private String addOrderByClause( String orderBy, boolean desc) {
|
588 |
|
|
if(orderBy==null){
|
589 |
|
|
return null;
|
590 |
|
|
}else if(orderBy.equals("date")){
|
591 |
|
|
return " order by claim.claim_date "+((desc)?"desc":"asc");
|
592 |
|
|
}else if(orderBy.equals("user")){
|
593 |
|
|
return " order by claim.claimedBy "+((desc)?"desc":"asc");
|
594 |
|
|
|
595 |
42789
|
argiro.kok
|
}else if(orderBy.equals("source")){
|
596 |
|
|
return " order by claim.source_title "+((desc)?"desc":"asc");
|
597 |
|
|
|
598 |
|
|
}else if(orderBy.equals("target")){
|
599 |
|
|
return " order by claim.target_title "+((desc)?"desc":"asc");
|
600 |
|
|
|
601 |
42214
|
argiro.kok
|
}else{
|
602 |
42762
|
argiro.kok
|
return " order by claim.claim_date desc";
|
603 |
42214
|
argiro.kok
|
}
|
604 |
42762
|
argiro.kok
|
|
605 |
42214
|
argiro.kok
|
}
|
606 |
42270
|
argiro.kok
|
|
607 |
48012
|
tsampikos.
|
private String addFilterByType( List<String> types, ArrayList<Object> params) {
|
608 |
50968
|
argiro.kok
|
logger.debug(types);
|
609 |
|
|
if(types == null || types.isEmpty() || (types.size() == 1 && types.get(0).isEmpty())){
|
610 |
42762
|
argiro.kok
|
return null;
|
611 |
52857
|
argiro.kok
|
}else if (types.contains(ClaimUtils.PUBLICATION) && types.contains(ClaimUtils.DATASET) && types.contains(ClaimUtils.SOFTWARE) && types.contains(ClaimUtils.PROJECT) && types.contains(ClaimUtils.CONTEXT) && types.contains(ClaimUtils.OTHER) ){
|
612 |
42762
|
argiro.kok
|
//it's all types - no need to filter
|
613 |
|
|
return null;
|
614 |
|
|
}
|
615 |
|
|
else{
|
616 |
|
|
String filter = "";
|
617 |
|
|
for(String type: types){
|
618 |
48012
|
tsampikos.
|
filter+=((filter.length()>0)?" or ":"")+" claim.source_type = ? or claim.target_type = ?";
|
619 |
|
|
params.add(type);
|
620 |
|
|
params.add(type);
|
621 |
42762
|
argiro.kok
|
}
|
622 |
|
|
return filter;
|
623 |
|
|
}
|
624 |
42270
|
argiro.kok
|
}
|
625 |
|
|
|
626 |
48012
|
tsampikos.
|
public String generateCountByUser(String user ,String keyword,List<String> types, ArrayList<Object> params) {
|
627 |
42762
|
argiro.kok
|
if((keyword == null || keyword.equals(""))&&types.isEmpty()){
|
628 |
48012
|
tsampikos.
|
params.add(user);
|
629 |
|
|
return " select count(*) from claim where "+ " claim.claimedBy=?";
|
630 |
42762
|
argiro.kok
|
}
|
631 |
48012
|
tsampikos.
|
return " select count(*) from claim where claim.id in ( select claim.id from ("+generateFetchClaimsByUser(user, null,null,keyword,null,false,types, params)+")as claim )";
|
632 |
42270
|
argiro.kok
|
}
|
633 |
48012
|
tsampikos.
|
public String generateCountByDate(String dateFrom, String dateTo ,String keyword,List<String> types, ArrayList<Object> params) {
|
634 |
42762
|
argiro.kok
|
if((keyword == null || keyword.equals(""))&&types.isEmpty()){
|
635 |
48012
|
tsampikos.
|
params.add(dateFrom);
|
636 |
|
|
params.add(dateTo);
|
637 |
|
|
return " select count(*) from claim where "+ " claim.claim_date >= ?::timestamp and claim.claim_date <= ?::timestamp ";
|
638 |
42762
|
argiro.kok
|
}
|
639 |
48012
|
tsampikos.
|
return " select count(*) from claim where claim.id in ( select claim.id from ("+generateFetchClaimsByDate(dateFrom,dateTo, null,null,keyword,null,false, types, params)+")as claim )";
|
640 |
42270
|
argiro.kok
|
}
|
641 |
48012
|
tsampikos.
|
public String generateCountByProject(String projectId ,String keyword, List<String> types, ArrayList<Object> params) {
|
642 |
42762
|
argiro.kok
|
if((keyword == null || keyword.equals(""))&&types.isEmpty()){
|
643 |
48012
|
tsampikos.
|
params.add(projectId);
|
644 |
|
|
return " select count(*) from claim where "+"claim.source_id =?";
|
645 |
42762
|
argiro.kok
|
}
|
646 |
48012
|
tsampikos.
|
return " select count(*) from claim where claim.id in ( select claim.id from ("+generateFetchClaimsByProject(projectId, null,null,keyword,null,false, types, params)+")as claim )";
|
647 |
42762
|
argiro.kok
|
}
|
648 |
47059
|
konstantin
|
/*
|
649 |
47039
|
argiro.kok
|
public String generateCountByProjectToken(String projectToken,String email ,String keyword, List<String> types ) {
|
650 |
|
|
return " select count(*) from claim where claim.id in ( select claim.id from ("+generateFetchClaimsByProjectToken(projectToken, email,null,null,keyword,null,false, types)+")as claim )";
|
651 |
|
|
}
|
652 |
47059
|
konstantin
|
*/
|
653 |
48012
|
tsampikos.
|
public String generateCountByFunder(String funderId ,String keyword, List<String> types, ArrayList<Object> params) {
|
654 |
42762
|
argiro.kok
|
if((keyword == null || keyword.equals(""))&&types.isEmpty()){
|
655 |
48012
|
tsampikos.
|
params.add(funderId);
|
656 |
|
|
return " select count(*) from claim,project as source where claim.source_id = source.openaire_id and source.funder_id =?";
|
657 |
42762
|
argiro.kok
|
}
|
658 |
48012
|
tsampikos.
|
return " select count(*) from claim where claim.id in ( select claim.id from ("+generateFetchClaimsByFunder(funderId, null,null,keyword,null,false, types, params)+")as claim )";
|
659 |
42762
|
argiro.kok
|
}
|
660 |
48012
|
tsampikos.
|
public String generateCountByContext(String contextId ,String keyword, List<String> types, ArrayList<Object> params) {
|
661 |
42762
|
argiro.kok
|
if((keyword == null || keyword.equals(""))&&types.isEmpty()){
|
662 |
50947
|
argiro.kok
|
params.add(contextId+"%");
|
663 |
|
|
return " select count(*) from claim where "+" claim.source_id like ?";
|
664 |
42762
|
argiro.kok
|
}
|
665 |
48012
|
tsampikos.
|
return " select count(*) from claim where claim.id in ( select claim.id from ("+generateFetchClaimsByContext(contextId, null,null,keyword,null,false, types, params)+")as claim )";
|
666 |
42270
|
argiro.kok
|
|
667 |
|
|
}
|
668 |
48012
|
tsampikos.
|
public String generateCountByResult(String resultId ,String keyword, List<String> types, ArrayList<Object> params) {
|
669 |
42762
|
argiro.kok
|
if((keyword == null || keyword.equals(""))&&types.isEmpty()){
|
670 |
48012
|
tsampikos.
|
params.add(resultId);
|
671 |
|
|
params.add(resultId);
|
672 |
|
|
return " select count(*) from claim where "+" ( claim.source_id= ? or claim.target_id = ? )";
|
673 |
42762
|
argiro.kok
|
}
|
674 |
48012
|
tsampikos.
|
return " select count(*) from claim where claim.id in ( select claim.id from ("+generateFetchClaimsByResult(resultId, null,null,keyword,null,false,types, params)+")as claim )";
|
675 |
42270
|
argiro.kok
|
|
676 |
|
|
}
|
677 |
|
|
|
678 |
48012
|
tsampikos.
|
public String generateCountAllClaims(String keyword,List<String> types, ArrayList<Object> params) {
|
679 |
42762
|
argiro.kok
|
if((keyword == null || keyword.equals(""))&&types.isEmpty()){
|
680 |
48012
|
tsampikos.
|
String filterByType = addFilterByType(types, params);
|
681 |
|
|
//if(filterByType!= null){
|
682 |
|
|
// params.add(filterByType);
|
683 |
|
|
//}
|
684 |
|
|
return "select count(*) from claim "+((filterByType==null)?"":" where " + filterByType);
|
685 |
42762
|
argiro.kok
|
}
|
686 |
48012
|
tsampikos.
|
return " select count(*) from claim where claim.id in ( select claim.id from ("+generateFetchClaims(null,null,keyword,null,false,types, params)+" ) as claim )";
|
687 |
42762
|
argiro.kok
|
|
688 |
42270
|
argiro.kok
|
}
|
689 |
42762
|
argiro.kok
|
public String generateFetchAllProjectsQuery() {
|
690 |
|
|
return " select "+getProjectFields("source")+" from project as source";
|
691 |
42270
|
argiro.kok
|
|
692 |
|
|
}
|
693 |
42762
|
argiro.kok
|
public String generateFetchAllContextsQuery() {
|
694 |
|
|
return " select "+getContextFields("source")+" from context as source";
|
695 |
|
|
}
|
696 |
48012
|
tsampikos.
|
public String generateFetchClaimsByUser(String user, Integer limit, Integer offset,String keyword,String orderBy, boolean desc,List<String> types, ArrayList<Object> params) {
|
697 |
42762
|
argiro.kok
|
// "claim.claim_date", true
|
698 |
48012
|
tsampikos.
|
String clause= " claim.claimedBy=?";
|
699 |
|
|
ArrayList<Object> clauseParams = new ArrayList<>();
|
700 |
|
|
clauseParams.add(user);
|
701 |
|
|
return generateSelectClaimQueryAsUnionOfAllRelations(limit, offset, orderBy,desc,clause,clauseParams, keyword, types, params);
|
702 |
41791
|
argiro.kok
|
|
703 |
|
|
}
|
704 |
42762
|
argiro.kok
|
/**
|
705 |
|
|
*
|
706 |
|
|
* @param limit how many results to return
|
707 |
|
|
* @param offset starting from
|
708 |
|
|
* @param keyword containing keyword
|
709 |
|
|
* @return
|
710 |
|
|
*/
|
711 |
42214
|
argiro.kok
|
|
712 |
48012
|
tsampikos.
|
public String generateFetchClaims(Integer limit, Integer offset,String keyword,String orderBy, boolean desc,List<String> types, ArrayList<Object> params) {
|
713 |
|
|
return generateSelectClaimQueryAsUnionOfAllRelations(limit, offset, orderBy,desc,null,null, keyword, types, params)+"";
|
714 |
42762
|
argiro.kok
|
}
|
715 |
42214
|
argiro.kok
|
/**
|
716 |
|
|
*
|
717 |
|
|
* @param limit how many results to return
|
718 |
|
|
* @param offset starting from
|
719 |
|
|
* @return
|
720 |
|
|
*/
|
721 |
48012
|
tsampikos.
|
public String generateFetchClaims(Integer limit, Integer offset, String orderBy, boolean desc,List<String> types, ArrayList<Object> params) {
|
722 |
42762
|
argiro.kok
|
String keyword = null;
|
723 |
48012
|
tsampikos.
|
return generateSelectClaimQueryAsUnionOfAllRelations(limit, offset, orderBy,desc,null,null,keyword,types, params)+"";
|
724 |
42214
|
argiro.kok
|
|
725 |
41791
|
argiro.kok
|
}
|
726 |
48012
|
tsampikos.
|
public String generateFetchClaimsByDate(String dateFrom, String dateTo, Integer limit, Integer offset,String keyword, String orderBy, boolean desc,List<String> types, ArrayList<Object> params) {
|
727 |
|
|
String clause= " claim.claim_date >= ?::timestamp and claim.claim_date <= ?::timestamp ";
|
728 |
|
|
ArrayList<Object> clauseParams = new ArrayList<>();
|
729 |
|
|
clauseParams.add(dateFrom);
|
730 |
|
|
clauseParams.add(dateTo);
|
731 |
|
|
return generateSelectClaimQueryAsUnionOfAllRelations(limit, offset, orderBy,desc,clause, clauseParams,keyword,types, params);
|
732 |
41791
|
argiro.kok
|
}
|
733 |
53200
|
konstantin
|
|
734 |
|
|
public String generateFetchNumberOfClaimsByDateAndOpenaireId(String dateFrom, String dateTo, String openaireId, Integer limit, Integer offset,String keyword, String orderBy, boolean desc,List<String> types, ArrayList<Object> params) {
|
735 |
|
|
String clause= " claim.claim_date >= ?::timestamp and claim.claim_date <= ?::timestamp and source.openaire_id =? ";
|
736 |
|
|
ArrayList<Object> clauseParams = new ArrayList<>();
|
737 |
|
|
clauseParams.add(dateFrom);
|
738 |
|
|
clauseParams.add(dateTo);
|
739 |
|
|
clauseParams.add(openaireId);
|
740 |
|
|
return generateSelectCountClaimQueryAsUnionOfAllRelations(limit, offset, orderBy,desc,clause, clauseParams,keyword,types, params);
|
741 |
|
|
}
|
742 |
|
|
|
743 |
48012
|
tsampikos.
|
public String generateFetchClaimsByProject(String projectId, Integer limit, Integer offset,String keyword, String orderBy, boolean desc, List<String> types, ArrayList<Object> params) {
|
744 |
|
|
String clause = "source.openaire_id =?";
|
745 |
|
|
ArrayList<Object> clauseParams = new ArrayList<>();
|
746 |
|
|
clauseParams.add(projectId);
|
747 |
50968
|
argiro.kok
|
String filterByType = addFilterByType(types, clauseParams);
|
748 |
|
|
clause+= ((filterByType==null)?"":" and ("+filterByType+") ");
|
749 |
48012
|
tsampikos.
|
String query = " select * from ("+" select * from ( " + generateSelectclaimQuery(ClaimUtils.PROJECT, ClaimUtils.PUBLICATION, limit, offset, orderBy, desc, clause, clauseParams, keyword, params) + " ) as claim "+
|
750 |
|
|
") as claim ";
|
751 |
50968
|
argiro.kok
|
|
752 |
48012
|
tsampikos.
|
return query;
|
753 |
42199
|
argiro.kok
|
}
|
754 |
47059
|
konstantin
|
/*
|
755 |
47039
|
argiro.kok
|
public String generateFetchClaimsByProjectToken(String token, String email, Integer limit, Integer offset,String keyword, String orderBy, boolean desc, List<String> types) {
|
756 |
47034
|
konstantin
|
String clause = "source.token = '" + token + "' and '" + email + "' = ANY(source.contact_person)";
|
757 |
|
|
String filterByType = addFilterByType(types);
|
758 |
|
|
return " select * from ("+" select * from ( " + generateSelectclaimQuery(ClaimUtils.PROJECT, ClaimUtils.PUBLICATION, limit, offset, orderBy, desc, clause, keyword) + " ) as claim "+
|
759 |
|
|
") as claim "+
|
760 |
|
|
((filterByType==null)?"":" where "+filterByType+" ");
|
761 |
|
|
}
|
762 |
47059
|
konstantin
|
*/
|
763 |
52619
|
konstantin
|
public String generateSelectProjectIdByTokenAndEmailQuery(String token, String email, ArrayList<Object> params) {
|
764 |
48012
|
tsampikos.
|
params.add(token);
|
765 |
|
|
params.add(email);
|
766 |
|
|
return "SELECT openaire_id FROM project WHERE token=? AND ?= ANY(contact_person);";
|
767 |
47059
|
konstantin
|
}
|
768 |
|
|
|
769 |
52619
|
konstantin
|
public String generateSelectProjectIdByTokenQuery(String token, ArrayList<Object> params) {
|
770 |
|
|
params.add(token);
|
771 |
|
|
return "SELECT openaire_id FROM project WHERE token=?;";
|
772 |
|
|
}
|
773 |
|
|
|
774 |
48012
|
tsampikos.
|
public String generateFetchClaimsByContext(String contextId, Integer limit, Integer offset,String keyword, String orderBy, boolean desc, List<String> types, ArrayList<Object> params) {
|
775 |
50947
|
argiro.kok
|
String clause= "claim.source_id like ?";
|
776 |
48012
|
tsampikos.
|
ArrayList<Object> clauseParams = new ArrayList<>();
|
777 |
50947
|
argiro.kok
|
clauseParams.add(contextId+'%');
|
778 |
50968
|
argiro.kok
|
String filterByType = addFilterByType(types, clauseParams);
|
779 |
|
|
clause+= ((filterByType==null)?"":" and ("+filterByType+") ");
|
780 |
48012
|
tsampikos.
|
String query = " select * from ("+" ( " +generateSelectclaimQuery(ClaimUtils.CONTEXT,ClaimUtils.PUBLICATION,limit, offset, orderBy,desc,clause,clauseParams, keyword, params)+")"+
|
781 |
50968
|
argiro.kok
|
") as claim ";
|
782 |
48012
|
tsampikos.
|
return query;
|
783 |
42214
|
argiro.kok
|
}
|
784 |
48012
|
tsampikos.
|
public String generateFetchClaimsByFunder(String funderId, Integer limit, Integer offset,String keyword, String orderBy, boolean desc, List<String> types, ArrayList<Object> params) {
|
785 |
42762
|
argiro.kok
|
String whereClause = "source_id =source.openaire_id ";
|
786 |
48012
|
tsampikos.
|
ArrayList<Object> whereParams = new ArrayList<>();
|
787 |
50968
|
argiro.kok
|
String filterByType = addFilterByType(types, whereParams);
|
788 |
|
|
whereClause+= ((filterByType==null)?"":" and ("+filterByType+") ");
|
789 |
48012
|
tsampikos.
|
String query = " select * from ("+" ( " +generateSelectclaimQuery(ClaimUtils.PROJECT,ClaimUtils.PUBLICATION, limit, offset, orderBy,desc,whereClause,whereParams, keyword, params)+" )"+
|
790 |
|
|
") as claim ";
|
791 |
41791
|
argiro.kok
|
|
792 |
50968
|
argiro.kok
|
|
793 |
48012
|
tsampikos.
|
return query;
|
794 |
41791
|
argiro.kok
|
}
|
795 |
48012
|
tsampikos.
|
public String generateFetchClaimsByResult(String resultId, Integer limit, Integer offset, String keyword, String orderBy, boolean desc, List<String> types, ArrayList<Object> params) {
|
796 |
|
|
String queryWhereClause=" ( source.openaire_id = ? or target.openaire_id = ? )";
|
797 |
|
|
ArrayList<Object> whereParams = new ArrayList<>();
|
798 |
|
|
whereParams.add(resultId);
|
799 |
|
|
whereParams.add(resultId);
|
800 |
50968
|
argiro.kok
|
String filterByType = addFilterByType(types, whereParams);
|
801 |
|
|
queryWhereClause+= ((filterByType==null)?"":" and ("+filterByType+") ");
|
802 |
48012
|
tsampikos.
|
String query = " select * from ("+" ( " +generateSelectclaimQuery(ClaimUtils.PUBLICATION,ClaimUtils.PUBLICATION, limit, offset, orderBy,desc,queryWhereClause,whereParams, keyword, params)+" ) \nunion "+
|
803 |
|
|
" ( " +generateSelectclaimQuery(ClaimUtils.PROJECT,ClaimUtils.PUBLICATION, limit, offset, orderBy,desc,queryWhereClause,whereParams, keyword, params)+" ) \nunion "+
|
804 |
|
|
" ( " +generateSelectclaimQuery(ClaimUtils.CONTEXT,ClaimUtils.PUBLICATION, limit, offset, orderBy,desc,queryWhereClause,whereParams, keyword, params)+" ) "+
|
805 |
|
|
") as claim ";
|
806 |
50968
|
argiro.kok
|
|
807 |
48012
|
tsampikos.
|
return query;
|
808 |
41791
|
argiro.kok
|
|
809 |
|
|
}
|
810 |
|
|
|
811 |
48012
|
tsampikos.
|
public String generateFetchClaimsByClaimId(String claimId, String sourceType, String targetType, ArrayList<Object> params) {
|
812 |
42762
|
argiro.kok
|
String keyword = null;
|
813 |
48012
|
tsampikos.
|
String clause= " claim.id = ?::int ";
|
814 |
|
|
ArrayList<Object> clauseParams = new ArrayList<>();
|
815 |
|
|
clauseParams.add(claimId);
|
816 |
42762
|
argiro.kok
|
String orderbyLimitClause= null;
|
817 |
52857
|
argiro.kok
|
if(sourceType.equals(ClaimUtils.PUBLICATION)||sourceType.equals(ClaimUtils.DATASET)||sourceType.equals(ClaimUtils.SOFTWARE)||sourceType.equals(ClaimUtils.OTHER)){
|
818 |
48012
|
tsampikos.
|
return generateSelectclaimQuery(sourceType,targetType,null,null,null,false, clause,clauseParams,keyword, params);
|
819 |
42270
|
argiro.kok
|
}else if(sourceType.equals(ClaimUtils.PROJECT)){
|
820 |
48012
|
tsampikos.
|
return generateSelectclaimQuery(sourceType,targetType,null,null,null,false, clause,clauseParams,keyword, params);
|
821 |
42270
|
argiro.kok
|
}else if(sourceType.equals(ClaimUtils.CONTEXT)){
|
822 |
48012
|
tsampikos.
|
return generateSelectclaimQuery(sourceType,targetType,null,null,null,false, clause,clauseParams,keyword, params);
|
823 |
42270
|
argiro.kok
|
}
|
824 |
|
|
return null;
|
825 |
|
|
}
|
826 |
41791
|
argiro.kok
|
|
827 |
42199
|
argiro.kok
|
private String getClaimFields() {
|
828 |
46977
|
argiro.kok
|
return " claim.id, claim.claim_date, claim.claimedBy, claim.source_type, claim.target_type, claim.semantics, claim.curation_date, claim.curated_by, claim.approved ";
|
829 |
41791
|
argiro.kok
|
}
|
830 |
42199
|
argiro.kok
|
private String getResultFields(String tableAlias) {
|
831 |
57029
|
argiro.kok
|
return " "+tableAlias+".openaire_id, "+tableAlias+".title "+tableAlias+"_title, "+tableAlias+".result_type, "+tableAlias+".doi, "+tableAlias+".orcidworkid, "+tableAlias+".access_rights, "+tableAlias+".embargo_end_date, "+tableAlias+".best_license, "+tableAlias+".external_url, "+tableAlias+".collected_from, "+tableAlias+".record_path, "+tableAlias+".record_format ";
|
832 |
41791
|
argiro.kok
|
}
|
833 |
42199
|
argiro.kok
|
private String getProjectFields(String tableAlias) {
|
834 |
57029
|
argiro.kok
|
return " "+tableAlias+".openaire_id, "+tableAlias+".name as "+tableAlias+"_title, "+tableAlias+".acronym, "+tableAlias+".funder_id, "+tableAlias+".funder_name, "+tableAlias+".funder_acronym, null as field7, array_to_string("+tableAlias+".contact_person,','), null as field9, null as field10, null as field11, null as field12 ";
|
835 |
41791
|
argiro.kok
|
}
|
836 |
42199
|
argiro.kok
|
private String getContextFields(String tableAlias) {
|
837 |
57029
|
argiro.kok
|
return " "+tableAlias+".openaire_id, "+tableAlias+".name "+tableAlias+"_title, "+" null as field4, null as field5, null as field6, null as field7, null as field8, null as field9, null as field10, null as field11, null as field12, null as field13 ";
|
838 |
41791
|
argiro.kok
|
}
|
839 |
47215
|
katerina.i
|
|
840 |
42199
|
argiro.kok
|
private String getFieldsPerType(String type, String tableAlias){
|
841 |
41791
|
argiro.kok
|
String fields= null;
|
842 |
|
|
|
843 |
47215
|
katerina.i
|
if (type != null) {
|
844 |
52857
|
argiro.kok
|
if (type.equals(ClaimUtils.PUBLICATION) || type.equals(ClaimUtils.DATASET) || type.equals(ClaimUtils.SOFTWARE)||type.equals(ClaimUtils.OTHER)) {
|
845 |
47215
|
katerina.i
|
fields = getResultFields(tableAlias);
|
846 |
|
|
|
847 |
|
|
} else if (type.equals(ClaimUtils.PROJECT)) {
|
848 |
|
|
fields = getProjectFields(tableAlias);
|
849 |
|
|
|
850 |
|
|
} else if (type.equals(ClaimUtils.CONTEXT)) {
|
851 |
|
|
fields = getContextFields(tableAlias);
|
852 |
|
|
}
|
853 |
41791
|
argiro.kok
|
}
|
854 |
47215
|
katerina.i
|
|
855 |
41791
|
argiro.kok
|
return fields;
|
856 |
|
|
}
|
857 |
48012
|
tsampikos.
|
//private String generateSelectclaimQuery(String sourceType, String targetType, Integer limit,Integer offset,String orderBy, boolean desc,String whereClause, String keyword, ArrayList<Object> params) {
|
858 |
|
|
private String generateSelectclaimQuery(String sourceType, String targetType, Integer limit,Integer offset,String orderBy, boolean desc,String whereClause, ArrayList<Object> whereParams, String keyword, ArrayList<Object> params) {
|
859 |
41791
|
argiro.kok
|
if(sourceType == null || targetType == null){
|
860 |
|
|
return null;
|
861 |
|
|
}
|
862 |
|
|
/*
|
863 |
|
|
" select "+getClaimFields()+" ,"+getFieldsPerType(sourceType, "source")+" , "+getFieldsPerType(targetType,"target")+
|
864 |
|
|
" \nfrom claim, "+getTypeForTable(sourceType)+" as source, "+getTypeForTable(targetType)+" as target \n" +
|
865 |
|
|
"where claim.source_id = source.openaire_id and claim.target_id = target.openaire_id "+((specificWhereClause==null)?"":" and "+specificWhereClause+" ");
|
866 |
|
|
*/
|
867 |
42762
|
argiro.kok
|
String orderByClause= addOrderByClause(orderBy,desc);
|
868 |
|
|
String pagingClause= addPagingClause(limit,offset);
|
869 |
48012
|
tsampikos.
|
String query = " select "+getClaimFields()+" ,"+getFieldsPerType(sourceType, "source")+" , "+getFieldsPerType(targetType,"target")+
|
870 |
41791
|
argiro.kok
|
" \nfrom claim, "+getTypeForTable(sourceType)+" as source, has_source_"+getTypeForTable(sourceType)+", has_target_"+getTypeForTable(targetType)+", "+getTypeForTable(targetType)+" as target \n" +
|
871 |
42762
|
argiro.kok
|
"where claim.id = has_source_"+getTypeForTable(sourceType)+".claim_id and has_source_"+getTypeForTable(sourceType)+".openaire_id = source.openaire_id " +
|
872 |
|
|
"and claim.id = has_target_"+getTypeForTable(targetType)+".claim_id and has_target_"+getTypeForTable(targetType)+".openaire_id = target.openaire_id "+
|
873 |
48012
|
tsampikos.
|
((keyword==null)?"":(" and ("+getKeywordClauseForType(sourceType, "source", keyword, params)+" or "+getKeywordClauseForType(targetType, "target", keyword, params)+" ")+" or "+getKeywordClauseForClaim( keyword, params)+" )")+
|
874 |
42762
|
argiro.kok
|
((whereClause==null)?"":" and "+whereClause+" ")+
|
875 |
|
|
((orderByClause==null)?"":" "+orderByClause+" ")+
|
876 |
|
|
pagingClause;
|
877 |
48012
|
tsampikos.
|
if(whereParams != null && !whereParams.isEmpty()) {
|
878 |
|
|
params.addAll(whereParams);
|
879 |
|
|
}
|
880 |
|
|
return query;
|
881 |
41791
|
argiro.kok
|
}
|
882 |
48012
|
tsampikos.
|
private String getKeywordClauseForType(String type,String tableAlias,String keyword, ArrayList<Object> params){
|
883 |
42762
|
argiro.kok
|
if (type == null ||keyword == null){
|
884 |
|
|
return "";
|
885 |
|
|
}
|
886 |
|
|
keyword=keyword.toLowerCase();
|
887 |
52857
|
argiro.kok
|
if (type.equals(ClaimUtils.PUBLICATION)||type.equals(ClaimUtils.DATASET)||type.equals(ClaimUtils.SOFTWARE)||type.equals(ClaimUtils.OTHER)){
|
888 |
48012
|
tsampikos.
|
params.add("%" + keyword + "%");
|
889 |
|
|
params.add("%" + keyword + "%");
|
890 |
|
|
return " (lower("+tableAlias+".title) like ? or lower("+tableAlias+".doi) like ?"+")";
|
891 |
42762
|
argiro.kok
|
}else if (type.equals(ClaimUtils.PROJECT)){
|
892 |
48012
|
tsampikos.
|
params.add("%" + keyword + "%");
|
893 |
|
|
params.add("%" + keyword + "%");
|
894 |
|
|
params.add("%" + keyword + "%");
|
895 |
|
|
params.add("%" + keyword + "%");
|
896 |
|
|
return " (lower("+tableAlias+".name) like ? or lower("+tableAlias+".acronym) like ? or lower("+tableAlias+".funder_name) like ? or lower("+tableAlias+".funder_acronym) like ? "+")";
|
897 |
42762
|
argiro.kok
|
}else if (type.equals(ClaimUtils.CONTEXT)){
|
898 |
48012
|
tsampikos.
|
params.add("%" + keyword + "%");
|
899 |
|
|
return " (lower("+tableAlias+".name) like ? )";
|
900 |
42762
|
argiro.kok
|
}
|
901 |
|
|
return "";
|
902 |
|
|
}
|
903 |
48012
|
tsampikos.
|
private String getKeywordClauseForClaim(String keyword, ArrayList<Object> params){
|
904 |
42762
|
argiro.kok
|
if (keyword == null){
|
905 |
|
|
return "";
|
906 |
|
|
}else {
|
907 |
48012
|
tsampikos.
|
params.add("%" + keyword.toLowerCase() + "%");
|
908 |
|
|
return " (lower(claim.claimedby) like ?)";
|
909 |
42762
|
argiro.kok
|
}
|
910 |
|
|
}
|
911 |
48012
|
tsampikos.
|
private String generateSelectClaimQueryAsUnionOfAllRelations(Integer limit,Integer offset,String orderBy, boolean desc,String specificWhereClause,ArrayList<Object> whereParams, String keyword,List<String> types, ArrayList<Object> params) {
|
912 |
41791
|
argiro.kok
|
//TODO eliminate unions based on the to
|
913 |
|
|
// ClaimUtils.PUBLICATION or ClaimUtils.DATASET it
|
914 |
42762
|
argiro.kok
|
String orderByClause= addOrderByClause(orderBy,desc);
|
915 |
|
|
String pagingClause= addPagingClause(limit,offset);
|
916 |
48012
|
tsampikos.
|
String query = "select * from ("+
|
917 |
41791
|
argiro.kok
|
|
918 |
48012
|
tsampikos.
|
" ( " +generateSelectclaimQuery(ClaimUtils.PUBLICATION,ClaimUtils.PUBLICATION,null,null,null,false, specificWhereClause,whereParams,keyword, params)+" ) \nunion "+
|
919 |
|
|
" ( " +generateSelectclaimQuery(ClaimUtils.DATASET,ClaimUtils.DATASET,null,null,null,false, specificWhereClause, whereParams,keyword, params)+" ) \nunion "+
|
920 |
49865
|
argiro.kok
|
" ( " +generateSelectclaimQuery(ClaimUtils.SOFTWARE,ClaimUtils.SOFTWARE,null,null,null,false, specificWhereClause, whereParams,keyword, params)+" ) \nunion "+
|
921 |
52857
|
argiro.kok
|
" ( " +generateSelectclaimQuery(ClaimUtils.OTHER,ClaimUtils.OTHER,null,null,null,false, specificWhereClause, whereParams,keyword, params)+" ) \nunion "+
|
922 |
48012
|
tsampikos.
|
" ( " +generateSelectclaimQuery(ClaimUtils.PROJECT,ClaimUtils.PUBLICATION, null,null,null,false, specificWhereClause, whereParams,keyword, params)+" ) \nunion "+
|
923 |
|
|
" ( " +generateSelectclaimQuery(ClaimUtils.CONTEXT,ClaimUtils.PUBLICATION,null,null,null,false, specificWhereClause, whereParams, keyword, params)+" )" +
|
924 |
41791
|
argiro.kok
|
|
925 |
48012
|
tsampikos.
|
") as claim ";
|
926 |
|
|
String filterByType = addFilterByType(types, params);
|
927 |
|
|
query += ((filterByType==null)?"":" where "+filterByType+" ")+
|
928 |
42762
|
argiro.kok
|
((orderByClause==null)?"":" "+orderByClause+" ")+
|
929 |
|
|
pagingClause;
|
930 |
|
|
|
931 |
48012
|
tsampikos.
|
return query;
|
932 |
41791
|
argiro.kok
|
}
|
933 |
53200
|
konstantin
|
private String generateSelectCountClaimQueryAsUnionOfAllRelations(Integer limit,Integer offset,String orderBy, boolean desc,String specificWhereClause,ArrayList<Object> whereParams, String keyword,List<String> types, ArrayList<Object> params) {
|
934 |
|
|
//TODO eliminate unions based on the to
|
935 |
|
|
// ClaimUtils.PUBLICATION or ClaimUtils.DATASET it
|
936 |
|
|
String orderByClause= addOrderByClause(orderBy,desc);
|
937 |
|
|
String pagingClause= addPagingClause(limit,offset);
|
938 |
|
|
String query = "select count(id) from ("+
|
939 |
|
|
|
940 |
|
|
" ( " +generateSelectclaimQuery(ClaimUtils.PUBLICATION,ClaimUtils.PUBLICATION,null,null,null,false, specificWhereClause,whereParams,keyword, params)+" ) \nunion "+
|
941 |
|
|
" ( " +generateSelectclaimQuery(ClaimUtils.DATASET,ClaimUtils.DATASET,null,null,null,false, specificWhereClause, whereParams,keyword, params)+" ) \nunion "+
|
942 |
|
|
" ( " +generateSelectclaimQuery(ClaimUtils.SOFTWARE,ClaimUtils.SOFTWARE,null,null,null,false, specificWhereClause, whereParams,keyword, params)+" ) \nunion "+
|
943 |
|
|
" ( " +generateSelectclaimQuery(ClaimUtils.OTHER,ClaimUtils.OTHER,null,null,null,false, specificWhereClause, whereParams,keyword, params)+" ) \nunion "+
|
944 |
|
|
" ( " +generateSelectclaimQuery(ClaimUtils.PROJECT,ClaimUtils.PUBLICATION, null,null,null,false, specificWhereClause, whereParams,keyword, params)+" ) \nunion "+
|
945 |
|
|
" ( " +generateSelectclaimQuery(ClaimUtils.CONTEXT,ClaimUtils.PUBLICATION,null,null,null,false, specificWhereClause, whereParams, keyword, params)+" )" +
|
946 |
|
|
|
947 |
|
|
") as claim ";
|
948 |
|
|
String filterByType = addFilterByType(types, params);
|
949 |
|
|
query += ((filterByType==null)?"":" where "+filterByType+" ")+
|
950 |
|
|
((orderByClause==null)?"":" "+orderByClause+" ")+
|
951 |
|
|
pagingClause;
|
952 |
|
|
|
953 |
|
|
return query;
|
954 |
|
|
}
|
955 |
|
|
|
956 |
42199
|
argiro.kok
|
private String getTypeForTable(String type){
|
957 |
41791
|
argiro.kok
|
if(type == null){
|
958 |
|
|
return null;
|
959 |
|
|
}
|
960 |
52857
|
argiro.kok
|
if(type.equals(ClaimUtils.PUBLICATION)||type.equals(ClaimUtils.DATASET) ||type.equals(ClaimUtils.SOFTWARE)||type.equals(ClaimUtils.OTHER)){
|
961 |
41791
|
argiro.kok
|
return "result";
|
962 |
|
|
}else{
|
963 |
|
|
return type;
|
964 |
|
|
}
|
965 |
|
|
}
|
966 |
|
|
/**
|
967 |
|
|
** Update - Queries *New* Database **
|
968 |
|
|
**/
|
969 |
|
|
|
970 |
42199
|
argiro.kok
|
private void updateResult(){
|
971 |
41791
|
argiro.kok
|
|
972 |
|
|
}
|
973 |
42199
|
argiro.kok
|
private void updateProject(){
|
974 |
41791
|
argiro.kok
|
|
975 |
|
|
}
|
976 |
|
|
/**
|
977 |
|
|
** Delete - Queries *New* Database **
|
978 |
|
|
**/
|
979 |
|
|
|
980 |
48012
|
tsampikos.
|
private String generateDeleteEntityQuery(String resultid, String claimId, String type, ArrayList<Object> params){
|
981 |
42199
|
argiro.kok
|
if(type == null||resultid == null||claimId == null ){
|
982 |
|
|
return null;
|
983 |
|
|
}
|
984 |
|
|
String query = null;
|
985 |
52857
|
argiro.kok
|
if(type.equals(ClaimUtils.PUBLICATION)||type.equals(ClaimUtils.DATASET) ||type.equals(ClaimUtils.SOFTWARE) ||type.equals(ClaimUtils.OTHER)){
|
986 |
48012
|
tsampikos.
|
query = generateDeleteResultQuery(resultid,claimId, params);
|
987 |
42199
|
argiro.kok
|
}else if(type.equals(ClaimUtils.PROJECT)){
|
988 |
48012
|
tsampikos.
|
query = generateDeleteProjectQuery(resultid,claimId, params);
|
989 |
42199
|
argiro.kok
|
}else if (type.equals(ClaimUtils.CONTEXT)){
|
990 |
48012
|
tsampikos.
|
query = generateDeleteContextQuery(resultid,claimId, params);
|
991 |
42199
|
argiro.kok
|
}
|
992 |
|
|
return query;
|
993 |
|
|
}
|
994 |
|
|
/*
|
995 |
|
|
Before delete check a Result, Project, Context
|
996 |
|
|
check if there is another claim in has_source_* or has_target_* relation
|
997 |
|
|
for the entity
|
998 |
|
|
*/
|
999 |
48012
|
tsampikos.
|
private String generateDeleteResultQuery(String resultid, String claimId, ArrayList<Object> params){
|
1000 |
|
|
params.add(resultid);
|
1001 |
|
|
String query = " DELETE FROM "+getTypeForTable(ClaimUtils.PUBLICATION)+" WHERE openaire_id = ? and NOT EXISTS \n" +
|
1002 |
|
|
" ( " +generateSelectOthersFromRelationTableQuery(resultid,claimId,getSourceTableName(ClaimUtils.PUBLICATION), params) + " union " +
|
1003 |
|
|
generateSelectOthersFromRelationTableQuery(resultid,claimId,getTargetTableName(ClaimUtils.PUBLICATION), params)+" ) ;\n" ;
|
1004 |
42199
|
argiro.kok
|
return query;
|
1005 |
|
|
}
|
1006 |
48012
|
tsampikos.
|
private String generateDeleteProjectQuery(String resultid, String claimId, ArrayList<Object> params){
|
1007 |
|
|
params.add(resultid);
|
1008 |
|
|
String query = " DELETE FROM "+getTypeForTable(ClaimUtils.PROJECT)+" WHERE openaire_id = ? and NOT EXISTS \n" +
|
1009 |
|
|
" ( " +generateSelectOthersFromRelationTableQuery(resultid,claimId,getSourceTableName(ClaimUtils.PROJECT), params) + " union " +
|
1010 |
|
|
generateSelectOthersFromRelationTableQuery(resultid,claimId,getTargetTableName(ClaimUtils.PROJECT), params)+" ) ;\n" ;
|
1011 |
42199
|
argiro.kok
|
return query;
|
1012 |
|
|
}
|
1013 |
48012
|
tsampikos.
|
private String generateDeleteContextQuery(String resultid, String claimId, ArrayList<Object> params){
|
1014 |
|
|
params.add(resultid);
|
1015 |
|
|
String query = " DELETE FROM "+getTypeForTable(ClaimUtils.CONTEXT)+" WHERE openaire_id = ? and NOT EXISTS \n" +
|
1016 |
|
|
" ( " +generateSelectOthersFromRelationTableQuery(resultid,claimId,getSourceTableName(ClaimUtils.CONTEXT), params) +" ) ;\n" ;
|
1017 |
42199
|
argiro.kok
|
return query;
|
1018 |
|
|
}
|
1019 |
|
|
|
1020 |
48294
|
argiro.kok
|
public String generateCountClaimsForResultQuery(String resultId){
|
1021 |
|
|
return " Select count(*) from claim where source_id = '"+ resultId +"' or target_id = '"+resultId+"' ";
|
1022 |
|
|
}
|
1023 |
|
|
public String generateSelectResultSource(String resultId){
|
1024 |
|
|
return " Select collected_from from result where openaire_id = '"+ resultId +"' ";
|
1025 |
|
|
}
|
1026 |
|
|
|
1027 |
42199
|
argiro.kok
|
/**
|
1028 |
|
|
* Select form a has_source_* or has_target_* table
|
1029 |
|
|
* ...
|
1030 |
|
|
* @param resultid
|
1031 |
|
|
* @param claimId
|
1032 |
|
|
* @param tableName
|
1033 |
|
|
* @return
|
1034 |
|
|
*/
|
1035 |
48012
|
tsampikos.
|
private String generateSelectOthersFromRelationTableQuery(String resultid, String claimId , String tableName, ArrayList<Object> params){
|
1036 |
|
|
params.add(resultid);
|
1037 |
|
|
params.add(claimId);
|
1038 |
42199
|
argiro.kok
|
String query =
|
1039 |
48012
|
tsampikos.
|
" SELECT 1 FROM "+ tableName+" WHERE openaire_id = ? and claim_id != ?::int " ;
|
1040 |
42199
|
argiro.kok
|
return query;
|
1041 |
|
|
}
|
1042 |
48012
|
tsampikos.
|
public String generateDeleteFullClaimQuery(String id, String user, String sourceType, String sourceId, String targetType, String targetId, ArrayList<Object> params){
|
1043 |
42199
|
argiro.kok
|
//TODO generate delete query
|
1044 |
48012
|
tsampikos.
|
params.add(id);
|
1045 |
|
|
params.add(id);
|
1046 |
|
|
params.add(id);
|
1047 |
42199
|
argiro.kok
|
String query =
|
1048 |
41791
|
argiro.kok
|
"BEGIN;\n" +
|
1049 |
42199
|
argiro.kok
|
//delete has_source_*
|
1050 |
48012
|
tsampikos.
|
"DELETE FROM "+ getSourceTableName(sourceType)+" WHERE claim_id = ?::int ;\n" +
|
1051 |
41791
|
argiro.kok
|
|
1052 |
42199
|
argiro.kok
|
//delete has_target_*
|
1053 |
48012
|
tsampikos.
|
"DELETE FROM "+ getTargetTableName(targetType)+" WHERE claim_id = ?::int ;\n" +
|
1054 |
41791
|
argiro.kok
|
|
1055 |
42199
|
argiro.kok
|
//delete claim
|
1056 |
48012
|
tsampikos.
|
"DELETE FROM claim where id = ?::int ;\n" +
|
1057 |
42199
|
argiro.kok
|
|
1058 |
|
|
//delete source
|
1059 |
48012
|
tsampikos.
|
generateDeleteEntityQuery(sourceId,id,sourceType, params)+
|
1060 |
42199
|
argiro.kok
|
|
1061 |
|
|
//delete target
|
1062 |
48012
|
tsampikos.
|
generateDeleteEntityQuery(targetId,id,targetType, params)+
|
1063 |
41791
|
argiro.kok
|
"COMMIT";
|
1064 |
42199
|
argiro.kok
|
return query;
|
1065 |
41791
|
argiro.kok
|
}
|
1066 |
48012
|
tsampikos.
|
public String generateSelectClaimQuery(String id, String user, ArrayList<Object> params){
|
1067 |
42199
|
argiro.kok
|
|
1068 |
48012
|
tsampikos.
|
params.add(id);
|
1069 |
|
|
params.add(user);
|
1070 |
42199
|
argiro.kok
|
String query =" ";
|
1071 |
48012
|
tsampikos.
|
return " Select id, source_type, source_id, target_type, target_id from claim where id = ?::int and claimedBy = ? ";
|
1072 |
42199
|
argiro.kok
|
}
|
1073 |
48294
|
argiro.kok
|
|
1074 |
48012
|
tsampikos.
|
public String generateSelectClaimQuery(String id, ArrayList<Object> params){
|
1075 |
42199
|
argiro.kok
|
|
1076 |
48012
|
tsampikos.
|
params.add(id);
|
1077 |
42270
|
argiro.kok
|
String query =" ";
|
1078 |
48012
|
tsampikos.
|
return " Select id, source_type, source_id, target_type, target_id,claimedBy from claim where id = ?::int";
|
1079 |
42270
|
argiro.kok
|
}
|
1080 |
42199
|
argiro.kok
|
/**
|
1081 |
|
|
* used to fetch the information from DB
|
1082 |
|
|
* @param ids
|
1083 |
|
|
* @param users
|
1084 |
|
|
* @return
|
1085 |
|
|
*/
|
1086 |
48012
|
tsampikos.
|
public String generateSelectClaimQuery(List<String> ids, List<String> users, ArrayList<Object> params){
|
1087 |
42199
|
argiro.kok
|
|
1088 |
|
|
String query =" Select id, source_type, source_id, target_type, target_id from claim where ";
|
1089 |
|
|
if(ids.size()>0 && users.size() > 0 && ids.size() == users.size()) {
|
1090 |
|
|
for (int i = 0; i < ids.size(); i++) {
|
1091 |
48012
|
tsampikos.
|
query+=" ( id = ? and claimedBy = ? ) or";
|
1092 |
|
|
params.add(ids.get(i));
|
1093 |
|
|
params.add(users.get(i));
|
1094 |
42199
|
argiro.kok
|
}
|
1095 |
|
|
query = query.substring(0, query.length()-2);
|
1096 |
|
|
return query;
|
1097 |
|
|
}
|
1098 |
|
|
return null;
|
1099 |
|
|
}
|
1100 |
46884
|
konstantin
|
|
1101 |
53200
|
konstantin
|
public String generateSelectFirstContextByCommunityIdQuery(String communityId, ArrayList<Object> params) {
|
1102 |
|
|
params.add(communityId+"%");
|
1103 |
53916
|
konstantin
|
return " Select " + getContextFields("context") + " from context where openaire_id like ? LIMIT 1;";
|
1104 |
53200
|
konstantin
|
}
|
1105 |
|
|
|
1106 |
48012
|
tsampikos.
|
public String generateSelectProjectByIdQuery(String projectId, ArrayList<Object> params) {
|
1107 |
|
|
params.add(projectId);
|
1108 |
|
|
return " Select " + getProjectFields("project") + " from project where openaire_id = ?";
|
1109 |
46884
|
konstantin
|
}
|
1110 |
|
|
|
1111 |
48012
|
tsampikos.
|
public String generateSelectContactEmailsByProjectIdQuery(String projectId, ArrayList<Object> params) {
|
1112 |
|
|
params.add(projectId);
|
1113 |
53200
|
konstantin
|
//return " SELECT contact_person FROM project WHERE openaire_id = ? ;";
|
1114 |
|
|
return "SELECT DISTINCT unnest(contact_person) as contact_person FROM project WHERE openaire_id = ?;";
|
1115 |
46884
|
konstantin
|
}
|
1116 |
|
|
|
1117 |
53200
|
konstantin
|
public String generateSelectProjectIdsAndNamesByProjectManagerMail(String userMail, ArrayList<Object> params) {
|
1118 |
|
|
params.add(userMail);
|
1119 |
|
|
return "SELECT openaire_id, name FROM project WHERE ? = ANY (contact_person);";
|
1120 |
|
|
}
|
1121 |
|
|
|
1122 |
48012
|
tsampikos.
|
public String generateUpdateTokenByProjectId(String projectdId, String newToken, ArrayList<Object> params) {
|
1123 |
|
|
params.add(newToken);
|
1124 |
|
|
params.add(projectdId);
|
1125 |
|
|
return "UPDATE project SET token = ? WHERE openaire_id = ?";
|
1126 |
46884
|
konstantin
|
}
|
1127 |
48012
|
tsampikos.
|
public String generateUpdateContactEmailsByProjectIdByProjectId(String projectdId, List<String> contactEmails, ArrayList<Object> params) {
|
1128 |
46918
|
argiro.kok
|
String emails = "";
|
1129 |
|
|
if(contactEmails != null && contactEmails.size() > 0){
|
1130 |
48012
|
tsampikos.
|
//emails+="'{";
|
1131 |
46918
|
argiro.kok
|
for(int i = 0; i< contactEmails.size(); i++){
|
1132 |
|
|
|
1133 |
48012
|
tsampikos.
|
emails+=contactEmails.get(i)+((i < contactEmails.size() -1 )?",":"");
|
1134 |
|
|
//params.add(contactEmails.get(i));
|
1135 |
46918
|
argiro.kok
|
}
|
1136 |
48012
|
tsampikos.
|
//emails+="}'";
|
1137 |
46918
|
argiro.kok
|
}
|
1138 |
|
|
|
1139 |
48012
|
tsampikos.
|
params.add(emails);
|
1140 |
|
|
params.add(projectdId);
|
1141 |
|
|
return "UPDATE project SET contact_person = string_to_array(?, ',') WHERE openaire_id = ?;";
|
1142 |
46918
|
argiro.kok
|
}
|
1143 |
46956
|
konstantin
|
|
1144 |
48012
|
tsampikos.
|
public String generateUpdateClaimCuration(String curatedBy, String claimId, boolean approved, ArrayList<Object> params) {
|
1145 |
46956
|
konstantin
|
SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
|
1146 |
|
|
Date date = new Date();
|
1147 |
|
|
String dateStr=null;
|
1148 |
|
|
dateStr=(format.format(date));
|
1149 |
|
|
|
1150 |
48012
|
tsampikos.
|
params.add(dateStr);
|
1151 |
|
|
params.add(curatedBy);
|
1152 |
|
|
params.add(approved);
|
1153 |
|
|
params.add(claimId);
|
1154 |
46956
|
konstantin
|
String query = "UPDATE claim "
|
1155 |
48012
|
tsampikos.
|
+ "SET curation_date = ?::timestamp, curated_by = ?, approved = ? "
|
1156 |
52619
|
konstantin
|
+ "WHERE id = ?::int";// RETURNING curation_date, curated_by, approved";
|
1157 |
46956
|
konstantin
|
|
1158 |
47219
|
argiro.kok
|
logger.debug("Query to execute: "+query);
|
1159 |
46956
|
konstantin
|
|
1160 |
|
|
return query;
|
1161 |
|
|
}
|
1162 |
|
|
|
1163 |
53200
|
konstantin
|
public String generateInsertNotificationQuery(Date date, String openaire_id, String userMail, int frequency, boolean notify , ArrayList<Object> params) {
|
1164 |
|
|
|
1165 |
|
|
SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
|
1166 |
|
|
String dateStr=null;
|
1167 |
|
|
dateStr=(format.format(date));
|
1168 |
|
|
String fields="last_interaction_date, user_email, frequency, notify, openaire_id";
|
1169 |
|
|
|
1170 |
|
|
String values="?::timestamp,?,?,?,?";
|
1171 |
|
|
params.add(dateStr);
|
1172 |
|
|
params.add(userMail);
|
1173 |
|
|
params.add(frequency);
|
1174 |
|
|
params.add(notify);
|
1175 |
|
|
params.add(openaire_id);
|
1176 |
|
|
|
1177 |
|
|
return " INSERT INTO notification( "+fields+")\n" +
|
1178 |
|
|
" VALUES ( "+values+")\n" +
|
1179 |
|
|
" RETURNING openaire_id, user_email\n";
|
1180 |
|
|
}
|
1181 |
|
|
|
1182 |
|
|
public String generateUpdateNotificationPreferences(String openaire_id, String userMail, int frequency, boolean notify, ArrayList<Object> params) {
|
1183 |
|
|
params.add(frequency);
|
1184 |
|
|
params.add(notify);
|
1185 |
|
|
params.add(userMail);
|
1186 |
|
|
params.add(openaire_id);
|
1187 |
|
|
String query = "UPDATE notification "
|
1188 |
|
|
+ "SET frequency = ?, notify = ? "
|
1189 |
|
|
+ "WHERE user_email = ? AND openaire_id = ?";// RETURNING curation_date, curated_by, approved";
|
1190 |
|
|
|
1191 |
|
|
logger.debug("Query to execute: "+query);
|
1192 |
|
|
|
1193 |
|
|
return query;
|
1194 |
|
|
}
|
1195 |
|
|
|
1196 |
|
|
public String generateUpdateNotificationLastInteractionDate(String openaire_id, String userMail, Date date, ArrayList<Object> params) {
|
1197 |
|
|
SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
|
1198 |
|
|
String dateStr=null;
|
1199 |
|
|
dateStr=(format.format(date));
|
1200 |
|
|
|
1201 |
|
|
params.add(dateStr);
|
1202 |
|
|
params.add(userMail);
|
1203 |
|
|
params.add(openaire_id);
|
1204 |
|
|
String query = "UPDATE notification "
|
1205 |
|
|
+ "SET last_interaction_date = ?::timestamp "
|
1206 |
|
|
+ "WHERE user_email = ? AND openaire_id = ?";// RETURNING curation_date, curated_by, approved";
|
1207 |
|
|
|
1208 |
|
|
logger.debug("Query to execute: "+query);
|
1209 |
|
|
|
1210 |
|
|
return query;
|
1211 |
|
|
}
|
1212 |
|
|
|
1213 |
|
|
public String generateSelectNotificationQuery(String openaire_id, String userMail, ArrayList<Object> params) {
|
1214 |
|
|
params.add(userMail);
|
1215 |
|
|
params.add(openaire_id);
|
1216 |
|
|
String query = "SELECT * FROM notification WHERE user_email = ? AND openaire_id = ?";
|
1217 |
|
|
|
1218 |
|
|
logger.debug("Query to execute: "+query);
|
1219 |
|
|
|
1220 |
|
|
return query;
|
1221 |
|
|
}
|
1222 |
|
|
|
1223 |
|
|
public String generateSelectTrueNotificationsQuery() {
|
1224 |
|
|
String query = "SELECT * FROM notification WHERE notify=true";
|
1225 |
|
|
|
1226 |
|
|
logger.debug("Query to execute: "+query);
|
1227 |
|
|
|
1228 |
|
|
return query;
|
1229 |
|
|
}
|
1230 |
|
|
|
1231 |
47039
|
argiro.kok
|
public String getMigrationTable() {
|
1232 |
|
|
return migrationTable;
|
1233 |
|
|
}
|
1234 |
|
|
|
1235 |
|
|
public void setMigrationTable(String migrationTable) {
|
1236 |
|
|
this.migrationTable = migrationTable;
|
1237 |
|
|
}
|
1238 |
39985
|
eri.katsar
|
}
|