Revision 48012
Added by Tsampikos Livisianos almost 7 years ago
| QueryGenerator.java | ||
|---|---|---|
| 2 | 2 |
|
| 3 | 3 |
import eu.dnetlib.data.claims.migration.entity.*; |
| 4 | 4 |
|
| 5 |
import java.lang.reflect.Array; |
|
| 5 | 6 |
import java.text.SimpleDateFormat; |
| 7 |
import java.util.ArrayList; |
|
| 6 | 8 |
import java.util.Date; |
| 7 | 9 |
import java.util.List; |
| 8 | 10 |
import org.apache.log4j.Logger; |
| ... | ... | |
| 24 | 26 |
/** |
| 25 | 27 |
** Insert Queries ** |
| 26 | 28 |
**/ |
| 27 |
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) {
|
|
| 29 |
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) {
|
|
| 28 | 30 |
if(title!=null&&title.contains("'")){
|
| 29 | 31 |
title=title.replace("'","''");
|
| 30 | 32 |
} |
| ... | ... | |
| 62 | 64 |
if(record_format!=null){
|
| 63 | 65 |
fields+=", record_format"; |
| 64 | 66 |
} |
| 65 |
String values="'"+openaire_id+"'"; |
|
| 67 |
String values="?"; |
|
| 68 |
params.add(openaire_id); |
|
| 66 | 69 |
if(result_type!=null){
|
| 67 |
values+=",'"+result_type+"'"; |
|
| 70 |
values+=",?"; |
|
| 71 |
params.add(result_type); |
|
| 68 | 72 |
} |
| 69 | 73 |
if(title!=null){
|
| 70 |
values+=",'"+title+"'"; |
|
| 74 |
values+=",?"; |
|
| 75 |
params.add(title); |
|
| 71 | 76 |
} |
| 72 | 77 |
if(collected_from!=null){
|
| 73 |
values+=",'"+collected_from+"'"; |
|
| 78 |
values+=",?"; |
|
| 79 |
params.add(collected_from); |
|
| 74 | 80 |
} |
| 75 | 81 |
if(external_url!=null){
|
| 76 |
values+=",'"+external_url+"'"; |
|
| 82 |
values+=",?"; |
|
| 83 |
params.add(external_url); |
|
| 77 | 84 |
} |
| 78 | 85 |
if(doi!=null){
|
| 79 |
values+=",'"+doi+"'"; |
|
| 86 |
values+=",?"; |
|
| 87 |
params.add(doi); |
|
| 80 | 88 |
} |
| 81 | 89 |
if(orcidworkid!=null){
|
| 82 |
values+=",'"+orcidworkid+"'"; |
|
| 90 |
values+=",?"; |
|
| 91 |
params.add(orcidworkid); |
|
| 83 | 92 |
} |
| 84 | 93 |
if(access_rights!=null){
|
| 85 |
values+=",'"+access_rights+"'"; |
|
| 94 |
values+=",?"; |
|
| 95 |
params.add(access_rights); |
|
| 86 | 96 |
} |
| 87 | 97 |
if(embargo_end_date!=null){
|
| 88 |
values+=",'"+embargo_end_date+"'"; |
|
| 98 |
values+=",?::date"; |
|
| 99 |
params.add(embargo_end_date); |
|
| 89 | 100 |
} |
| 90 | 101 |
if(best_license!=null){
|
| 91 |
values+=",'"+best_license+"'"; |
|
| 102 |
values+=",?"; |
|
| 103 |
params.add(best_license); |
|
| 92 | 104 |
} |
| 93 | 105 |
if(record_path!=null){
|
| 94 |
values+=",'"+record_path+"'"; |
|
| 106 |
values+=",?"; |
|
| 107 |
params.add(record_path); |
|
| 95 | 108 |
} |
| 96 | 109 |
if(record_format!=null){
|
| 97 |
values+=",'"+record_format+"'"; |
|
| 110 |
values+=",?"; |
|
| 111 |
params.add(record_format); |
|
| 98 | 112 |
} |
| 113 |
params.add(openaire_id); |
|
| 99 | 114 |
return "INSERT INTO result("+fields+")\n" +
|
| 100 | 115 |
" Select "+values+ |
| 101 |
" where not exists (select openaire_id from result where openaire_id='"+openaire_id+"')\n" +
|
|
| 116 |
" where not exists (select openaire_id from result where openaire_id=?)\n" +
|
|
| 102 | 117 |
" RETURNING openaire_id"; |
| 103 | 118 |
//TODO update on insert when exists |
| 104 | 119 |
} |
| 105 |
public String generateInsertProjectQuery(String openaire_id, String name, String acronym, String funder_id, String funder_name, String funder_acronym, List<String> contactEmails) {
|
|
| 120 |
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) {
|
|
| 106 | 121 |
if(name!=null) {
|
| 107 | 122 |
name = name.replace("'", "''");
|
| 108 | 123 |
} |
| ... | ... | |
| 125 | 140 |
if(contactEmails != null && contactEmails.size() > 0){
|
| 126 | 141 |
fields+=",contact_person"; |
| 127 | 142 |
} |
| 128 |
String values="'"+openaire_id+"'"; |
|
| 143 |
String values="?"; |
|
| 144 |
params.add(openaire_id); |
|
| 129 | 145 |
if(name!=null){
|
| 130 |
values+=",'"+name+"'"; |
|
| 146 |
values+=",?"; |
|
| 147 |
params.add(name); |
|
| 131 | 148 |
} |
| 132 | 149 |
if(acronym!=null){
|
| 133 |
values+=",'"+acronym+"'"; |
|
| 150 |
values+=",?"; |
|
| 151 |
params.add(acronym); |
|
| 134 | 152 |
} |
| 135 | 153 |
if(funder_id!=null){
|
| 136 |
values+=",'"+funder_id+"'"; |
|
| 154 |
values+=",?"; |
|
| 155 |
params.add(funder_id); |
|
| 137 | 156 |
} |
| 138 | 157 |
if(funder_name!=null){
|
| 139 |
values+=",'"+funder_name+"'"; |
|
| 158 |
values+=",?"; |
|
| 159 |
params.add(funder_name); |
|
| 140 | 160 |
} |
| 141 | 161 |
if(funder_acronym!=null){
|
| 142 |
values+=",'"+funder_acronym+"'"; |
|
| 162 |
values+=",?"; |
|
| 163 |
params.add(funder_acronym); |
|
| 143 | 164 |
} |
| 144 | 165 |
if(contactEmails != null && contactEmails.size() > 0){
|
| 145 |
values+=",'{";
|
|
| 166 |
values+=",string_to_array(?, ',')"; |
|
| 167 |
//String email_json = "{";
|
|
| 168 |
String email_json = ""; |
|
| 146 | 169 |
for(int i = 0; i< contactEmails.size(); i++){
|
| 147 |
|
|
| 148 |
values+="\""+contactEmails.get(i)+"\""+((i < contactEmails.size() -1 )?",":"");
|
|
| 170 |
email_json+=contactEmails.get(i)+((i < contactEmails.size() -1 )?",":""); |
|
| 171 |
//params.add(contactEmails.get(i));
|
|
| 149 | 172 |
} |
| 150 |
values+="}'"; |
|
| 173 |
//email_json+=""; |
|
| 174 |
params.add(email_json); |
|
| 151 | 175 |
} |
| 176 |
params.add(openaire_id); |
|
| 152 | 177 |
return "INSERT INTO project("+fields+")\n" +
|
| 153 | 178 |
" Select "+values + |
| 154 |
" where not exists (select openaire_id from project where openaire_id='"+openaire_id+"')\n" +
|
|
| 179 |
" where not exists (select openaire_id from project where openaire_id=?)\n" +
|
|
| 155 | 180 |
" RETURNING openaire_id"; |
| 156 | 181 |
//TODO update on insert when exists |
| 157 | 182 |
} |
| 158 |
private String generateInsertContextQuery(String openaire_id, String name) {
|
|
| 183 |
private String generateInsertContextQuery(String openaire_id, String name, ArrayList<Object> params) {
|
|
| 159 | 184 |
String fields="openaire_id"; |
| 160 | 185 |
if(name!=null){
|
| 161 | 186 |
fields+=",name"; |
| 162 | 187 |
} |
| 163 |
String values="'"+openaire_id+"'"; |
|
| 188 |
String values="?"; |
|
| 189 |
params.add(openaire_id); |
|
| 164 | 190 |
if(name!=null){
|
| 165 |
values+=",'"+name+"'"; |
|
| 191 |
values+=",?"; |
|
| 192 |
params.add(name); |
|
| 166 | 193 |
} |
| 194 |
params.add(openaire_id); |
|
| 167 | 195 |
return "INSERT INTO context("+fields+")\n" +
|
| 168 | 196 |
" Select "+values+ |
| 169 |
" where not exists (select openaire_id from context where openaire_id='"+openaire_id+"')\n" +
|
|
| 197 |
" where not exists (select openaire_id from context where openaire_id=?)\n" +
|
|
| 170 | 198 |
" RETURNING openaire_id"; |
| 171 | 199 |
//TODO update on insert when exists |
| 172 | 200 |
} |
| 173 |
private String generateInsertClaimQuery(Date date, String claimedBy, String source_type, String target_type , String source_id , String target_id, String semantics) {
|
|
| 201 |
private String generateInsertClaimQuery(Date date, String claimedBy, String source_type, String target_type , String source_id , String target_id, String semantics, ArrayList<Object> params) {
|
|
| 174 | 202 |
|
| 175 | 203 |
SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
|
| 176 | 204 |
String dateStr=null; |
| ... | ... | |
| 179 | 207 |
if(semantics!=null){
|
| 180 | 208 |
fields+=",semantics"; |
| 181 | 209 |
} |
| 182 |
String values="'"+dateStr+"','"+claimedBy+"','"+source_type+"', '"+target_type+"','"+source_id+"','"+target_id+"'"; |
|
| 210 |
String values="?::timestamp,?,?,?,?,?"; |
|
| 211 |
params.add(dateStr); |
|
| 212 |
params.add(claimedBy); |
|
| 213 |
params.add(source_type); |
|
| 214 |
params.add(target_type); |
|
| 215 |
params.add(source_id); |
|
| 216 |
params.add(target_id); |
|
| 183 | 217 |
if(semantics!=null){
|
| 184 |
values+=",'"+semantics+"'"; |
|
| 218 |
values+=",?"; |
|
| 219 |
params.add(semantics); |
|
| 185 | 220 |
} |
| 186 | 221 |
|
| 187 | 222 |
return " INSERT INTO claim( "+fields+")\n" + |
| ... | ... | |
| 189 | 224 |
" RETURNING id, source_id, target_id\n"; |
| 190 | 225 |
} |
| 191 | 226 |
|
| 192 |
private String generateInsertFullClaimQuery(String targetQuery, String sourceQuery, Claim claim) {
|
|
| 227 |
private String generateInsertFullClaimQuery(String targetQuery, String sourceQuery, Claim claim, ArrayList<Object> params) {
|
|
| 193 | 228 |
if (claim ==null || targetQuery ==null || sourceQuery == null){
|
| 194 | 229 |
return null; |
| 195 | 230 |
} |
| ... | ... | |
| 203 | 238 |
" ),\n source AS (\n" + |
| 204 | 239 |
sourceQuery+ |
| 205 | 240 |
" ),\n myclaim AS (\n" + |
| 206 |
generateInsertClaimQuery(claim.getDate(), claim.getUserMail(), claim.getSourceType(), claim.getTargetType(), claim.getSource().getOpenaireId(), claim.getTarget().getOpenaireId(),this.createSemanticsType(claim.getSourceType(),claim.getTargetType())) + |
|
| 241 |
generateInsertClaimQuery(claim.getDate(), claim.getUserMail(), claim.getSourceType(), claim.getTargetType(), claim.getSource().getOpenaireId(), claim.getTarget().getOpenaireId(),this.createSemanticsType(claim.getSourceType(),claim.getTargetType()), params) +
|
|
| 207 | 242 |
" ),\n ins4 AS (\n" + |
| 208 | 243 |
" INSERT INTO "+sourceTableName+" (claim_id, openaire_id)\n" + |
| 209 | 244 |
" SELECT id, source_id\n" + |
| ... | ... | |
| 213 | 248 |
" FROM myclaim "+ |
| 214 | 249 |
" RETURNING claim_id;"; |
| 215 | 250 |
} |
| 216 |
public String generateInsertFullClaimQuery( Claim claim) {
|
|
| 251 |
public String generateInsertFullClaimQuery( Claim claim, ArrayList<Object> params) {
|
|
| 217 | 252 |
String targetQuery =null; |
| 218 | 253 |
String sourceQuery=null; |
| 219 |
targetQuery=generateInsertEntityQuery(claim.getTarget(),claim.getTargetType()); |
|
| 220 |
sourceQuery=generateInsertEntityQuery(claim.getSource(),claim.getSourceType()); |
|
| 221 |
return generateInsertFullClaimQuery(targetQuery,sourceQuery,claim); |
|
| 254 |
targetQuery=generateInsertEntityQuery(claim.getTarget(),claim.getTargetType(), params);
|
|
| 255 |
sourceQuery=generateInsertEntityQuery(claim.getSource(),claim.getSourceType(), params);
|
|
| 256 |
return generateInsertFullClaimQuery(targetQuery,sourceQuery,claim, params);
|
|
| 222 | 257 |
|
| 223 | 258 |
} |
| 224 |
private String generateInsertEntityQuery(OpenaireEntity openaireEntity, String type){
|
|
| 259 |
private String generateInsertEntityQuery(OpenaireEntity openaireEntity, String type, ArrayList<Object> params){
|
|
| 225 | 260 |
String query=null; |
| 226 | 261 |
if(type==null){
|
| 227 | 262 |
return null; |
| 228 | 263 |
} |
| 229 | 264 |
if(type.equals(ClaimUtils.DATASET)||type.equals(ClaimUtils.PUBLICATION)){
|
| 230 | 265 |
Result result=(Result)openaireEntity; |
| 231 |
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()); |
|
| 266 |
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);
|
|
| 232 | 267 |
}else if(type.equals(ClaimUtils.PROJECT)){
|
| 233 | 268 |
Project project=(Project)openaireEntity; |
| 234 |
query=generateInsertProjectQuery(project.getOpenaireId(),project.getName(),project.getAcronym(),project.getFunderId(),project.getFunderName(),project.getFunderShortName(),project.getContactEmails()); |
|
| 269 |
query=generateInsertProjectQuery(project.getOpenaireId(),project.getName(),project.getAcronym(),project.getFunderId(),project.getFunderName(),project.getFunderShortName(),project.getContactEmails(), params);
|
|
| 235 | 270 |
}else if(type.equals(ClaimUtils.CONTEXT)){
|
| 236 | 271 |
Context context= (Context)openaireEntity; |
| 237 |
query=generateInsertContextQuery(context.getOpenaireId(),context.getTitle()); |
|
| 272 |
query=generateInsertContextQuery(context.getOpenaireId(),context.getTitle(), params);
|
|
| 238 | 273 |
} |
| 239 | 274 |
|
| 240 | 275 |
return query; |
| ... | ... | |
| 258 | 293 |
} |
| 259 | 294 |
return tablename; |
| 260 | 295 |
} |
| 261 |
public String generateInsertClaimResultQuery(String targetQuery, String sourceQuery, Claim claim) {
|
|
| 296 |
public String generateInsertClaimResultQuery(String targetQuery, String sourceQuery, Claim claim, ArrayList<Object> params) {
|
|
| 262 | 297 |
|
| 263 | 298 |
return " WITH ins1 AS (\n" + |
| 264 | 299 |
targetQuery+ |
| 265 | 300 |
" ),\n ins2 AS (\n" + |
| 266 | 301 |
sourceQuery+ |
| 267 | 302 |
" ),\n ins3 AS (\n" + |
| 268 |
generateInsertClaimQuery(claim.getDate(), claim.getUserMail(), claim.getSourceType(), claim.getTargetType(), claim.getSource().getOpenaireId(), claim.getTarget().getOpenaireId(),this.createSemanticsType(claim.getSourceType(),claim.getTargetType())) + |
|
| 303 |
generateInsertClaimQuery(claim.getDate(), claim.getUserMail(), claim.getSourceType(), claim.getTargetType(), claim.getSource().getOpenaireId(), claim.getTarget().getOpenaireId(),this.createSemanticsType(claim.getSourceType(),claim.getTargetType()), params) +
|
|
| 269 | 304 |
" ),\n ins4 AS (\n" + |
| 270 | 305 |
" INSERT INTO has_source_result (claim_id, openaire_id)\n" + |
| 271 | 306 |
" SELECT id, source_id\n" + |
| ... | ... | |
| 512 | 547 |
limit=10; |
| 513 | 548 |
offset=0; |
| 514 | 549 |
} |
| 515 |
return " limit "+limit+ " offset "+offset;
|
|
| 550 |
return " limit " + limit + " offset " + offset;
|
|
| 516 | 551 |
|
| 517 | 552 |
} |
| 518 | 553 |
private String addOrderByClause( String orderBy, boolean desc) {
|
| ... | ... | |
| 535 | 570 |
|
| 536 | 571 |
} |
| 537 | 572 |
|
| 538 |
private String addFilterByType( List<String> types) {
|
|
| 573 |
private String addFilterByType( List<String> types, ArrayList<Object> params) {
|
|
| 539 | 574 |
if(types == null || types.isEmpty()){
|
| 540 | 575 |
return null; |
| 541 | 576 |
}else if (types.contains(ClaimUtils.PUBLICATION) && types.contains(ClaimUtils.DATASET) && types.contains(ClaimUtils.PROJECT) && types.contains(ClaimUtils.CONTEXT) ){
|
| ... | ... | |
| 545 | 580 |
else{
|
| 546 | 581 |
String filter = ""; |
| 547 | 582 |
for(String type: types){
|
| 548 |
filter+=((filter.length()>0)?" or ":"")+" claim.source_type = '"+type+"' or claim.target_type = '"+type+"' "; |
|
| 583 |
filter+=((filter.length()>0)?" or ":"")+" claim.source_type = ? or claim.target_type = ?"; |
|
| 584 |
params.add(type); |
|
| 585 |
params.add(type); |
|
| 549 | 586 |
} |
| 550 | 587 |
return filter; |
| 551 | 588 |
} |
| 552 | 589 |
} |
| 553 | 590 |
|
| 554 |
public String generateCountByUser(String user ,String keyword,List<String> types ) {
|
|
| 591 |
public String generateCountByUser(String user ,String keyword,List<String> types, ArrayList<Object> params) {
|
|
| 555 | 592 |
if((keyword == null || keyword.equals(""))&&types.isEmpty()){
|
| 556 |
return " select count(*) from claim where "+ " claim.claimedBy='" + user + "'"; |
|
| 593 |
params.add(user); |
|
| 594 |
return " select count(*) from claim where "+ " claim.claimedBy=?"; |
|
| 557 | 595 |
} |
| 558 |
return " select count(*) from claim where claim.id in ( select claim.id from ("+generateFetchClaimsByUser(user, null,null,keyword,null,false,types)+")as claim )";
|
|
| 596 |
return " select count(*) from claim where claim.id in ( select claim.id from ("+generateFetchClaimsByUser(user, null,null,keyword,null,false,types, params)+")as claim )";
|
|
| 559 | 597 |
} |
| 560 |
public String generateCountByDate(String dateFrom, String dateTo ,String keyword,List<String> types) {
|
|
| 598 |
public String generateCountByDate(String dateFrom, String dateTo ,String keyword,List<String> types, ArrayList<Object> params) {
|
|
| 561 | 599 |
if((keyword == null || keyword.equals(""))&&types.isEmpty()){
|
| 562 |
return " select count(*) from claim where "+ " claim.claim_date >= '" + dateFrom + "' and claim.claim_date <= '" + dateTo + "' "; |
|
| 600 |
params.add(dateFrom); |
|
| 601 |
params.add(dateTo); |
|
| 602 |
return " select count(*) from claim where "+ " claim.claim_date >= ?::timestamp and claim.claim_date <= ?::timestamp "; |
|
| 563 | 603 |
} |
| 564 |
return " select count(*) from claim where claim.id in ( select claim.id from ("+generateFetchClaimsByDate(dateFrom,dateTo, null,null,keyword,null,false, types)+")as claim )";
|
|
| 604 |
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 )";
|
|
| 565 | 605 |
} |
| 566 |
public String generateCountByProject(String projectId ,String keyword, List<String> types ) {
|
|
| 606 |
public String generateCountByProject(String projectId ,String keyword, List<String> types, ArrayList<Object> params) {
|
|
| 567 | 607 |
if((keyword == null || keyword.equals(""))&&types.isEmpty()){
|
| 568 |
return " select count(*) from claim where "+"claim.source_id ='" + projectId + "'"; |
|
| 608 |
params.add(projectId); |
|
| 609 |
return " select count(*) from claim where "+"claim.source_id =?"; |
|
| 569 | 610 |
} |
| 570 |
return " select count(*) from claim where claim.id in ( select claim.id from ("+generateFetchClaimsByProject(projectId, null,null,keyword,null,false, types)+")as claim )";
|
|
| 611 |
return " select count(*) from claim where claim.id in ( select claim.id from ("+generateFetchClaimsByProject(projectId, null,null,keyword,null,false, types, params)+")as claim )";
|
|
| 571 | 612 |
} |
| 572 | 613 |
/* |
| 573 | 614 |
public String generateCountByProjectToken(String projectToken,String email ,String keyword, List<String> types ) {
|
| 574 | 615 |
return " select count(*) from claim where claim.id in ( select claim.id from ("+generateFetchClaimsByProjectToken(projectToken, email,null,null,keyword,null,false, types)+")as claim )";
|
| 575 | 616 |
} |
| 576 | 617 |
*/ |
| 577 |
public String generateCountByFunder(String funderId ,String keyword, List<String> types ) {
|
|
| 618 |
public String generateCountByFunder(String funderId ,String keyword, List<String> types, ArrayList<Object> params) {
|
|
| 578 | 619 |
if((keyword == null || keyword.equals(""))&&types.isEmpty()){
|
| 579 |
return " select count(*) from claim,project as source where claim.source_id = source.openaire_id and source.funder_id ='" + funderId + "'"; |
|
| 620 |
params.add(funderId); |
|
| 621 |
return " select count(*) from claim,project as source where claim.source_id = source.openaire_id and source.funder_id =?"; |
|
| 580 | 622 |
} |
| 581 |
return " select count(*) from claim where claim.id in ( select claim.id from ("+generateFetchClaimsByFunder(funderId, null,null,keyword,null,false, types)+")as claim )";
|
|
| 623 |
return " select count(*) from claim where claim.id in ( select claim.id from ("+generateFetchClaimsByFunder(funderId, null,null,keyword,null,false, types, params)+")as claim )";
|
|
| 582 | 624 |
} |
| 583 |
public String generateCountByContext(String contextId ,String keyword, List<String> types ) {
|
|
| 625 |
public String generateCountByContext(String contextId ,String keyword, List<String> types, ArrayList<Object> params) {
|
|
| 584 | 626 |
if((keyword == null || keyword.equals(""))&&types.isEmpty()){
|
| 585 |
return " select count(*) from claim where "+" claim.source_id ='" + contextId + "'"; |
|
| 627 |
params.add(contextId); |
|
| 628 |
return " select count(*) from claim where "+" claim.source_id =?"; |
|
| 586 | 629 |
} |
| 587 |
return " select count(*) from claim where claim.id in ( select claim.id from ("+generateFetchClaimsByContext(contextId, null,null,keyword,null,false, types)+")as claim )";
|
|
| 630 |
return " select count(*) from claim where claim.id in ( select claim.id from ("+generateFetchClaimsByContext(contextId, null,null,keyword,null,false, types, params)+")as claim )";
|
|
| 588 | 631 |
|
| 589 | 632 |
} |
| 590 |
public String generateCountByResult(String resultId ,String keyword, List<String> types) {
|
|
| 633 |
public String generateCountByResult(String resultId ,String keyword, List<String> types, ArrayList<Object> params) {
|
|
| 591 | 634 |
if((keyword == null || keyword.equals(""))&&types.isEmpty()){
|
| 592 |
return " select count(*) from claim where "+" ( claim.source_id= '"+resultId+"' or claim.target_id = '"+resultId+"' )"; |
|
| 635 |
params.add(resultId); |
|
| 636 |
params.add(resultId); |
|
| 637 |
return " select count(*) from claim where "+" ( claim.source_id= ? or claim.target_id = ? )"; |
|
| 593 | 638 |
} |
| 594 |
return " select count(*) from claim where claim.id in ( select claim.id from ("+generateFetchClaimsByResult(resultId, null,null,keyword,null,false,types)+")as claim )";
|
|
| 639 |
return " select count(*) from claim where claim.id in ( select claim.id from ("+generateFetchClaimsByResult(resultId, null,null,keyword,null,false,types, params)+")as claim )";
|
|
| 595 | 640 |
|
| 596 | 641 |
} |
| 597 | 642 |
|
| 598 |
public String generateCountAllClaims(String keyword,List<String> types) {
|
|
| 599 |
String filterByType = addFilterByType(types); |
|
| 643 |
public String generateCountAllClaims(String keyword,List<String> types, ArrayList<Object> params) {
|
|
| 600 | 644 |
if((keyword == null || keyword.equals(""))&&types.isEmpty()){
|
| 601 |
return "select count(*) from claim "+((filterByType==null)?"":" where "+filterByType+" "); |
|
| 645 |
String filterByType = addFilterByType(types, params); |
|
| 646 |
//if(filterByType!= null){
|
|
| 647 |
// params.add(filterByType); |
|
| 648 |
//} |
|
| 649 |
return "select count(*) from claim "+((filterByType==null)?"":" where " + filterByType); |
|
| 602 | 650 |
} |
| 603 |
return " select count(*) from claim where claim.id in ( select claim.id from ("+generateFetchClaims(null,null,keyword,null,false,types)+" ) as claim )";
|
|
| 651 |
return " select count(*) from claim where claim.id in ( select claim.id from ("+generateFetchClaims(null,null,keyword,null,false,types, params)+" ) as claim )";
|
|
| 604 | 652 |
|
| 605 | 653 |
} |
| 606 | 654 |
public String generateFetchAllProjectsQuery() {
|
| ... | ... | |
| 610 | 658 |
public String generateFetchAllContextsQuery() {
|
| 611 | 659 |
return " select "+getContextFields("source")+" from context as source";
|
| 612 | 660 |
} |
| 613 |
public String generateFetchClaimsByUser(String user, Integer limit, Integer offset,String keyword,String orderBy, boolean desc,List<String> types) {
|
|
| 661 |
public String generateFetchClaimsByUser(String user, Integer limit, Integer offset,String keyword,String orderBy, boolean desc,List<String> types, ArrayList<Object> params) {
|
|
| 614 | 662 |
// "claim.claim_date", true |
| 615 |
String clause= " claim.claimedBy='" + user + "'"; |
|
| 616 |
return generateSelectClaimQueryAsUnionOfAllRelations(limit, offset, orderBy,desc,clause,keyword, types); |
|
| 663 |
String clause= " claim.claimedBy=?"; |
|
| 664 |
ArrayList<Object> clauseParams = new ArrayList<>(); |
|
| 665 |
clauseParams.add(user); |
|
| 666 |
return generateSelectClaimQueryAsUnionOfAllRelations(limit, offset, orderBy,desc,clause,clauseParams, keyword, types, params); |
|
| 617 | 667 |
|
| 618 | 668 |
} |
| 619 | 669 |
/** |
| ... | ... | |
| 624 | 674 |
* @return |
| 625 | 675 |
*/ |
| 626 | 676 |
|
| 627 |
public String generateFetchClaims(Integer limit, Integer offset,String keyword,String orderBy, boolean desc,List<String> types) {
|
|
| 628 |
return generateSelectClaimQueryAsUnionOfAllRelations(limit, offset, orderBy,desc,null,keyword, types)+"";
|
|
| 677 |
public String generateFetchClaims(Integer limit, Integer offset,String keyword,String orderBy, boolean desc,List<String> types, ArrayList<Object> params) {
|
|
| 678 |
return generateSelectClaimQueryAsUnionOfAllRelations(limit, offset, orderBy,desc,null,null, keyword, types, params)+"";
|
|
| 629 | 679 |
} |
| 630 | 680 |
/** |
| 631 | 681 |
* |
| ... | ... | |
| 633 | 683 |
* @param offset starting from |
| 634 | 684 |
* @return |
| 635 | 685 |
*/ |
| 636 |
public String generateFetchClaims(Integer limit, Integer offset, String orderBy, boolean desc,List<String> types) {
|
|
| 686 |
public String generateFetchClaims(Integer limit, Integer offset, String orderBy, boolean desc,List<String> types, ArrayList<Object> params) {
|
|
| 637 | 687 |
String keyword = null; |
| 638 |
return generateSelectClaimQueryAsUnionOfAllRelations(limit, offset, orderBy,desc,null,keyword,types)+"";
|
|
| 688 |
return generateSelectClaimQueryAsUnionOfAllRelations(limit, offset, orderBy,desc,null,null,keyword,types, params)+"";
|
|
| 639 | 689 |
|
| 640 | 690 |
} |
| 641 |
public String generateFetchClaimsByDate(String dateFrom, String dateTo, Integer limit, Integer offset,String keyword, String orderBy, boolean desc,List<String> types) {
|
|
| 642 |
String clause= " claim.claim_date >= '" + dateFrom + "' and claim.claim_date <= '" + dateTo + "' "; |
|
| 643 |
return generateSelectClaimQueryAsUnionOfAllRelations(limit, offset, orderBy,desc,clause,keyword,types); |
|
| 691 |
public String generateFetchClaimsByDate(String dateFrom, String dateTo, Integer limit, Integer offset,String keyword, String orderBy, boolean desc,List<String> types, ArrayList<Object> params) {
|
|
| 692 |
String clause= " claim.claim_date >= ?::timestamp and claim.claim_date <= ?::timestamp "; |
|
| 693 |
ArrayList<Object> clauseParams = new ArrayList<>(); |
|
| 694 |
clauseParams.add(dateFrom); |
|
| 695 |
clauseParams.add(dateTo); |
|
| 696 |
return generateSelectClaimQueryAsUnionOfAllRelations(limit, offset, orderBy,desc,clause, clauseParams,keyword,types, params); |
|
| 644 | 697 |
} |
| 645 |
public String generateFetchClaimsByProject(String projectId, Integer limit, Integer offset,String keyword, String orderBy, boolean desc, List<String> types) {
|
|
| 646 |
String clause = "source.openaire_id ='" + projectId + "'"; |
|
| 647 |
String filterByType = addFilterByType(types); |
|
| 648 |
return " select * from ("+" select * from ( " + generateSelectclaimQuery(ClaimUtils.PROJECT, ClaimUtils.PUBLICATION, limit, offset, orderBy, desc, clause, keyword) + " ) as claim "+
|
|
| 649 |
") as claim "+ |
|
| 650 |
((filterByType==null)?"":" where "+filterByType+" "); |
|
| 698 |
public String generateFetchClaimsByProject(String projectId, Integer limit, Integer offset,String keyword, String orderBy, boolean desc, List<String> types, ArrayList<Object> params) {
|
|
| 699 |
String clause = "source.openaire_id =?"; |
|
| 700 |
ArrayList<Object> clauseParams = new ArrayList<>(); |
|
| 701 |
clauseParams.add(projectId); |
|
| 702 |
String query = " select * from ("+" select * from ( " + generateSelectclaimQuery(ClaimUtils.PROJECT, ClaimUtils.PUBLICATION, limit, offset, orderBy, desc, clause, clauseParams, keyword, params) + " ) as claim "+
|
|
| 703 |
") as claim "; |
|
| 704 |
String filterByType = addFilterByType(types, params); |
|
| 705 |
query += ((filterByType==null)?"":" where "+filterByType+" "); |
|
| 706 |
return query; |
|
| 651 | 707 |
} |
| 652 | 708 |
/* |
| 653 | 709 |
public String generateFetchClaimsByProjectToken(String token, String email, Integer limit, Integer offset,String keyword, String orderBy, boolean desc, List<String> types) {
|
| ... | ... | |
| 658 | 714 |
((filterByType==null)?"":" where "+filterByType+" "); |
| 659 | 715 |
} |
| 660 | 716 |
*/ |
| 661 |
public String generateSelectProjectIdByTokenQuery(String token, String email) {
|
|
| 662 |
return "SELECT openaire_id FROM project WHERE token='"+token+"' AND '"+email+"'= ANY(contact_person);"; |
|
| 717 |
public String generateSelectProjectIdByTokenQuery(String token, String email, ArrayList<Object> params) {
|
|
| 718 |
params.add(token); |
|
| 719 |
params.add(email); |
|
| 720 |
return "SELECT openaire_id FROM project WHERE token=? AND ?= ANY(contact_person);"; |
|
| 663 | 721 |
} |
| 664 | 722 |
|
| 665 |
public String generateFetchClaimsByContext(String contextId, Integer limit, Integer offset,String keyword, String orderBy, boolean desc, List<String> types) {
|
|
| 666 |
String clause= "claim.source_id ='" + contextId + "'"; |
|
| 667 |
String filterByType = addFilterByType(types); |
|
| 668 |
return " select * from ("+" ( " +generateSelectclaimQuery(ClaimUtils.CONTEXT,ClaimUtils.PUBLICATION,limit, offset, orderBy,desc,clause,keyword)+")"+
|
|
| 669 |
") as claim "+ |
|
| 670 |
((filterByType==null)?"":" where "+filterByType+" "); |
|
| 723 |
public String generateFetchClaimsByContext(String contextId, Integer limit, Integer offset,String keyword, String orderBy, boolean desc, List<String> types, ArrayList<Object> params) {
|
|
| 724 |
String clause= "claim.source_id =?"; |
|
| 725 |
ArrayList<Object> clauseParams = new ArrayList<>(); |
|
| 726 |
clauseParams.add(contextId); |
|
| 727 |
String query = " select * from ("+" ( " +generateSelectclaimQuery(ClaimUtils.CONTEXT,ClaimUtils.PUBLICATION,limit, offset, orderBy,desc,clause,clauseParams, keyword, params)+")"+
|
|
| 728 |
") as claim "; |
|
| 729 |
String filterByType = addFilterByType(types, params); |
|
| 730 |
query += ((filterByType==null)?"":" where "+filterByType+" "); |
|
| 731 |
return query; |
|
| 671 | 732 |
} |
| 672 |
public String generateFetchClaimsByFunder(String funderId, Integer limit, Integer offset,String keyword, String orderBy, boolean desc, List<String> types) {
|
|
| 733 |
public String generateFetchClaimsByFunder(String funderId, Integer limit, Integer offset,String keyword, String orderBy, boolean desc, List<String> types, ArrayList<Object> params) {
|
|
| 673 | 734 |
String whereClause = "source_id =source.openaire_id "; |
| 674 |
String filterByType = addFilterByType(types); |
|
| 675 |
return " select * from ("+" ( " +generateSelectclaimQuery(ClaimUtils.PROJECT,ClaimUtils.PUBLICATION, limit, offset, orderBy,desc,whereClause,keyword)+" )"+
|
|
| 676 |
") as claim "+ |
|
| 677 |
((filterByType==null)?"":" where "+filterByType+" "); |
|
| 735 |
ArrayList<Object> whereParams = new ArrayList<>(); |
|
| 736 |
String query = " select * from ("+" ( " +generateSelectclaimQuery(ClaimUtils.PROJECT,ClaimUtils.PUBLICATION, limit, offset, orderBy,desc,whereClause,whereParams, keyword, params)+" )"+
|
|
| 737 |
") as claim "; |
|
| 738 |
String filterByType = addFilterByType(types, params); |
|
| 739 |
query += ((filterByType==null)?"":" where "+filterByType+" "); |
|
| 678 | 740 |
|
| 679 |
|
|
| 741 |
return query; |
|
| 680 | 742 |
} |
| 681 |
public String generateFetchClaimsByResult(String resultId, Integer limit, Integer offset, String keyword, String orderBy, boolean desc, List<String> types) {
|
|
| 682 |
String queryWhereClause=" ( source.openaire_id = '"+resultId+"' or target.openaire_id = '"+resultId+"' )"; |
|
| 683 |
String filterByType = addFilterByType(types); |
|
| 684 |
return " select * from ("+" ( " +generateSelectclaimQuery(ClaimUtils.PUBLICATION,ClaimUtils.PUBLICATION, limit, offset, orderBy,desc,queryWhereClause,keyword)+" ) \nunion "+
|
|
| 685 |
" ( " +generateSelectclaimQuery(ClaimUtils.PROJECT,ClaimUtils.PUBLICATION, limit, offset, orderBy,desc,queryWhereClause,keyword)+" ) \nunion "+ |
|
| 686 |
" ( " +generateSelectclaimQuery(ClaimUtils.CONTEXT,ClaimUtils.PUBLICATION, limit, offset, orderBy,desc,queryWhereClause,keyword)+" ) "+ |
|
| 687 |
") as claim "+ |
|
| 688 |
((filterByType==null)?"":" where "+filterByType+" "); |
|
| 743 |
public String generateFetchClaimsByResult(String resultId, Integer limit, Integer offset, String keyword, String orderBy, boolean desc, List<String> types, ArrayList<Object> params) {
|
|
| 744 |
String queryWhereClause=" ( source.openaire_id = ? or target.openaire_id = ? )"; |
|
| 745 |
ArrayList<Object> whereParams = new ArrayList<>(); |
|
| 746 |
whereParams.add(resultId); |
|
| 747 |
whereParams.add(resultId); |
|
| 748 |
String query = " select * from ("+" ( " +generateSelectclaimQuery(ClaimUtils.PUBLICATION,ClaimUtils.PUBLICATION, limit, offset, orderBy,desc,queryWhereClause,whereParams, keyword, params)+" ) \nunion "+
|
|
| 749 |
" ( " +generateSelectclaimQuery(ClaimUtils.PROJECT,ClaimUtils.PUBLICATION, limit, offset, orderBy,desc,queryWhereClause,whereParams, keyword, params)+" ) \nunion "+ |
|
| 750 |
" ( " +generateSelectclaimQuery(ClaimUtils.CONTEXT,ClaimUtils.PUBLICATION, limit, offset, orderBy,desc,queryWhereClause,whereParams, keyword, params)+" ) "+ |
|
| 751 |
") as claim "; |
|
| 752 |
String filterByType = addFilterByType(types, params); |
|
| 753 |
query += ((filterByType==null)?"":" where "+filterByType+" "); |
|
| 754 |
return query; |
|
| 689 | 755 |
|
| 690 | 756 |
} |
| 691 | 757 |
|
| 692 |
public String generateFetchClaimsByClaimId(String claimId, String sourceType, String targetType) {
|
|
| 758 |
public String generateFetchClaimsByClaimId(String claimId, String sourceType, String targetType, ArrayList<Object> params) {
|
|
| 693 | 759 |
String keyword = null; |
| 694 |
String clause= " claim.id = '" +claimId + "' "; |
|
| 760 |
String clause= " claim.id = ?::int "; |
|
| 761 |
ArrayList<Object> clauseParams = new ArrayList<>(); |
|
| 762 |
clauseParams.add(claimId); |
|
| 695 | 763 |
String orderbyLimitClause= null; |
| 696 | 764 |
if(sourceType.equals(ClaimUtils.PUBLICATION)||sourceType.equals(ClaimUtils.DATASET)){
|
| 697 |
return generateSelectclaimQuery(sourceType,targetType,null,null,null,false, clause,keyword);
|
|
| 765 |
return generateSelectclaimQuery(sourceType,targetType,null,null,null,false, clause,clauseParams,keyword, params);
|
|
| 698 | 766 |
}else if(sourceType.equals(ClaimUtils.PROJECT)){
|
| 699 |
return generateSelectclaimQuery(sourceType,targetType,null,null,null,false, clause,keyword);
|
|
| 767 |
return generateSelectclaimQuery(sourceType,targetType,null,null,null,false, clause,clauseParams,keyword, params);
|
|
| 700 | 768 |
}else if(sourceType.equals(ClaimUtils.CONTEXT)){
|
| 701 |
return generateSelectclaimQuery(sourceType,targetType,null,null,null,false, clause,keyword);
|
|
| 769 |
return generateSelectclaimQuery(sourceType,targetType,null,null,null,false, clause,clauseParams,keyword, params);
|
|
| 702 | 770 |
} |
| 703 | 771 |
return null; |
| 704 | 772 |
} |
| ... | ... | |
| 733 | 801 |
|
| 734 | 802 |
return fields; |
| 735 | 803 |
} |
| 736 |
private String generateSelectclaimQuery(String sourceType, String targetType, Integer limit,Integer offset,String orderBy, boolean desc,String whereClause, String keyword) {
|
|
| 804 |
//private String generateSelectclaimQuery(String sourceType, String targetType, Integer limit,Integer offset,String orderBy, boolean desc,String whereClause, String keyword, ArrayList<Object> params) {
|
|
| 805 |
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) {
|
|
| 737 | 806 |
if(sourceType == null || targetType == null){
|
| 738 | 807 |
return null; |
| 739 | 808 |
} |
| ... | ... | |
| 744 | 813 |
*/ |
| 745 | 814 |
String orderByClause= addOrderByClause(orderBy,desc); |
| 746 | 815 |
String pagingClause= addPagingClause(limit,offset); |
| 747 |
return " select "+getClaimFields()+" ,"+getFieldsPerType(sourceType, "source")+" , "+getFieldsPerType(targetType,"target")+
|
|
| 816 |
String query = " select "+getClaimFields()+" ,"+getFieldsPerType(sourceType, "source")+" , "+getFieldsPerType(targetType,"target")+
|
|
| 748 | 817 |
" \nfrom claim, "+getTypeForTable(sourceType)+" as source, has_source_"+getTypeForTable(sourceType)+", has_target_"+getTypeForTable(targetType)+", "+getTypeForTable(targetType)+" as target \n" + |
| 749 | 818 |
"where claim.id = has_source_"+getTypeForTable(sourceType)+".claim_id and has_source_"+getTypeForTable(sourceType)+".openaire_id = source.openaire_id " + |
| 750 | 819 |
"and claim.id = has_target_"+getTypeForTable(targetType)+".claim_id and has_target_"+getTypeForTable(targetType)+".openaire_id = target.openaire_id "+ |
| 751 |
((keyword==null)?"":(" and ("+getKeywordClauseForType(sourceType, "source", keyword)+" or "+getKeywordClauseForType(targetType, "target", keyword)+" ")+" or "+getKeywordClauseForClaim( keyword)+" )")+
|
|
| 820 |
((keyword==null)?"":(" and ("+getKeywordClauseForType(sourceType, "source", keyword, params)+" or "+getKeywordClauseForType(targetType, "target", keyword, params)+" ")+" or "+getKeywordClauseForClaim( keyword, params)+" )")+
|
|
| 752 | 821 |
((whereClause==null)?"":" and "+whereClause+" ")+ |
| 753 | 822 |
((orderByClause==null)?"":" "+orderByClause+" ")+ |
| 754 | 823 |
pagingClause; |
| 824 |
if(whereParams != null && !whereParams.isEmpty()) {
|
|
| 825 |
params.addAll(whereParams); |
|
| 826 |
} |
|
| 827 |
return query; |
|
| 755 | 828 |
} |
| 756 |
private String getKeywordClauseForType(String type,String tableAlias,String keyword){
|
|
| 829 |
private String getKeywordClauseForType(String type,String tableAlias,String keyword, ArrayList<Object> params){
|
|
| 757 | 830 |
if (type == null ||keyword == null){
|
| 758 | 831 |
return ""; |
| 759 | 832 |
} |
| 760 | 833 |
keyword=keyword.toLowerCase(); |
| 761 | 834 |
if (type.equals(ClaimUtils.PUBLICATION)||type.equals(ClaimUtils.DATASET)){
|
| 762 |
return " (lower("+tableAlias+".title) like '%"+keyword+"%' or lower("+tableAlias+".doi) like '%"+keyword+"%'"+")";
|
|
| 835 |
params.add("%" + keyword + "%");
|
|
| 836 |
params.add("%" + keyword + "%");
|
|
| 837 |
return " (lower("+tableAlias+".title) like ? or lower("+tableAlias+".doi) like ?"+")";
|
|
| 763 | 838 |
}else if (type.equals(ClaimUtils.PROJECT)){
|
| 764 |
return " (lower("+tableAlias+".name) like '%"+keyword+"%' or lower("+tableAlias+".acronym) like '%"+keyword+"%' or lower("+tableAlias+".funder_name) like '%"+keyword+"%' or lower("+tableAlias+".funder_acronym) like '%"+keyword+"%' "+")";
|
|
| 839 |
params.add("%" + keyword + "%");
|
|
| 840 |
params.add("%" + keyword + "%");
|
|
| 841 |
params.add("%" + keyword + "%");
|
|
| 842 |
params.add("%" + keyword + "%");
|
|
| 843 |
return " (lower("+tableAlias+".name) like ? or lower("+tableAlias+".acronym) like ? or lower("+tableAlias+".funder_name) like ? or lower("+tableAlias+".funder_acronym) like ? "+")";
|
|
| 765 | 844 |
}else if (type.equals(ClaimUtils.CONTEXT)){
|
| 766 |
return " (lower("+tableAlias+".name) like '%"+keyword+"%' )";
|
|
| 845 |
params.add("%" + keyword + "%");
|
|
| 846 |
return " (lower("+tableAlias+".name) like ? )";
|
|
| 767 | 847 |
} |
| 768 | 848 |
return ""; |
| 769 | 849 |
} |
| 770 |
private String getKeywordClauseForClaim(String keyword){
|
|
| 850 |
private String getKeywordClauseForClaim(String keyword, ArrayList<Object> params){
|
|
| 771 | 851 |
if (keyword == null){
|
| 772 | 852 |
return ""; |
| 773 | 853 |
}else {
|
| 774 |
return " (lower(claim.claimedby) like '%"+keyword.toLowerCase()+"%')"; |
|
| 854 |
params.add("%" + keyword.toLowerCase() + "%");
|
|
| 855 |
return " (lower(claim.claimedby) like ?)"; |
|
| 775 | 856 |
} |
| 776 | 857 |
} |
| 777 |
private String generateSelectClaimQueryAsUnionOfAllRelations(Integer limit,Integer offset,String orderBy, boolean desc,String specificWhereClause,String keyword,List<String> types) {
|
|
| 858 |
private String generateSelectClaimQueryAsUnionOfAllRelations(Integer limit,Integer offset,String orderBy, boolean desc,String specificWhereClause,ArrayList<Object> whereParams, String keyword,List<String> types, ArrayList<Object> params) {
|
|
| 778 | 859 |
//TODO eliminate unions based on the to |
| 779 | 860 |
// ClaimUtils.PUBLICATION or ClaimUtils.DATASET it |
| 780 | 861 |
String orderByClause= addOrderByClause(orderBy,desc); |
| 781 | 862 |
String pagingClause= addPagingClause(limit,offset); |
| 782 |
String filterByType = addFilterByType(types); |
|
| 783 |
return "select * from ("+
|
|
| 863 |
String query = "select * from ("+
|
|
| 784 | 864 |
|
| 785 |
" ( " +generateSelectclaimQuery(ClaimUtils.PUBLICATION,ClaimUtils.PUBLICATION,null,null,null,false, specificWhereClause,keyword)+" ) \nunion "+
|
|
| 786 |
" ( " +generateSelectclaimQuery(ClaimUtils.DATASET,ClaimUtils.DATASET,null,null,null,false, specificWhereClause,keyword)+" ) \nunion "+
|
|
| 787 |
" ( " +generateSelectclaimQuery(ClaimUtils.PROJECT,ClaimUtils.PUBLICATION, null,null,null,false, specificWhereClause,keyword)+" ) \nunion "+
|
|
| 788 |
" ( " +generateSelectclaimQuery(ClaimUtils.CONTEXT,ClaimUtils.PUBLICATION,null,null,null,false, specificWhereClause,keyword)+" )" +
|
|
| 865 |
" ( " +generateSelectclaimQuery(ClaimUtils.PUBLICATION,ClaimUtils.PUBLICATION,null,null,null,false, specificWhereClause,whereParams,keyword, params)+" ) \nunion "+
|
|
| 866 |
" ( " +generateSelectclaimQuery(ClaimUtils.DATASET,ClaimUtils.DATASET,null,null,null,false, specificWhereClause, whereParams,keyword, params)+" ) \nunion "+
|
|
| 867 |
" ( " +generateSelectclaimQuery(ClaimUtils.PROJECT,ClaimUtils.PUBLICATION, null,null,null,false, specificWhereClause, whereParams,keyword, params)+" ) \nunion "+
|
|
| 868 |
" ( " +generateSelectclaimQuery(ClaimUtils.CONTEXT,ClaimUtils.PUBLICATION,null,null,null,false, specificWhereClause, whereParams, keyword, params)+" )" +
|
|
| 789 | 869 |
|
| 790 |
") as claim "+ |
|
| 791 |
((filterByType==null)?"":" where "+filterByType+" ")+ |
|
| 870 |
") as claim "; |
|
| 871 |
String filterByType = addFilterByType(types, params); |
|
| 872 |
query += ((filterByType==null)?"":" where "+filterByType+" ")+ |
|
| 792 | 873 |
((orderByClause==null)?"":" "+orderByClause+" ")+ |
| 793 | 874 |
pagingClause; |
| 794 | 875 |
|
| 795 |
|
|
| 876 |
return query; |
|
| 796 | 877 |
} |
| 797 | 878 |
private String getTypeForTable(String type){
|
| 798 | 879 |
if(type == null){
|
| ... | ... | |
| 818 | 899 |
** Delete - Queries *New* Database ** |
| 819 | 900 |
**/ |
| 820 | 901 |
|
| 821 |
private String generateDeleteEntityQuery(String resultid, String claimId, String type){
|
|
| 902 |
private String generateDeleteEntityQuery(String resultid, String claimId, String type, ArrayList<Object> params){
|
|
| 822 | 903 |
if(type == null||resultid == null||claimId == null ){
|
| 823 | 904 |
return null; |
| 824 | 905 |
} |
| 825 | 906 |
String query = null; |
| 826 | 907 |
if(type.equals(ClaimUtils.PUBLICATION)||type.equals(ClaimUtils.DATASET)){
|
| 827 |
query = generateDeleteResultQuery(resultid,claimId); |
|
| 908 |
query = generateDeleteResultQuery(resultid,claimId, params);
|
|
| 828 | 909 |
}else if(type.equals(ClaimUtils.PROJECT)){
|
| 829 |
query = generateDeleteProjectQuery(resultid,claimId); |
|
| 910 |
query = generateDeleteProjectQuery(resultid,claimId, params);
|
|
| 830 | 911 |
}else if (type.equals(ClaimUtils.CONTEXT)){
|
| 831 |
query = generateDeleteContextQuery(resultid,claimId); |
|
| 912 |
query = generateDeleteContextQuery(resultid,claimId, params);
|
|
| 832 | 913 |
} |
| 833 | 914 |
return query; |
| 834 | 915 |
} |
| ... | ... | |
| 837 | 918 |
check if there is another claim in has_source_* or has_target_* relation |
| 838 | 919 |
for the entity |
| 839 | 920 |
*/ |
| 840 |
private String generateDeleteResultQuery(String resultid, String claimId ){
|
|
| 841 |
String query = " DELETE FROM "+getTypeForTable(ClaimUtils.PUBLICATION)+" WHERE openaire_id = '"+resultid+"' and NOT EXISTS \n" + |
|
| 842 |
" ( " +generateSelectOthersFromRelationTableQuery(resultid,claimId,getSourceTableName(ClaimUtils.PUBLICATION)) + " union " + |
|
| 843 |
generateSelectOthersFromRelationTableQuery(resultid,claimId,getTargetTableName(ClaimUtils.PUBLICATION))+" ) ;\n" ; |
|
| 921 |
private String generateDeleteResultQuery(String resultid, String claimId, ArrayList<Object> params){
|
|
| 922 |
params.add(resultid); |
|
| 923 |
String query = " DELETE FROM "+getTypeForTable(ClaimUtils.PUBLICATION)+" WHERE openaire_id = ? and NOT EXISTS \n" + |
|
| 924 |
" ( " +generateSelectOthersFromRelationTableQuery(resultid,claimId,getSourceTableName(ClaimUtils.PUBLICATION), params) + " union " + |
|
| 925 |
generateSelectOthersFromRelationTableQuery(resultid,claimId,getTargetTableName(ClaimUtils.PUBLICATION), params)+" ) ;\n" ; |
|
| 844 | 926 |
return query; |
| 845 | 927 |
} |
| 846 |
private String generateDeleteProjectQuery(String resultid, String claimId ){
|
|
| 847 |
String query = " DELETE FROM "+getTypeForTable(ClaimUtils.PROJECT)+" WHERE openaire_id = '"+resultid+"' and NOT EXISTS \n" + |
|
| 848 |
" ( " +generateSelectOthersFromRelationTableQuery(resultid,claimId,getSourceTableName(ClaimUtils.PROJECT)) + " union " + |
|
| 849 |
generateSelectOthersFromRelationTableQuery(resultid,claimId,getTargetTableName(ClaimUtils.PROJECT))+" ) ;\n" ; |
|
| 928 |
private String generateDeleteProjectQuery(String resultid, String claimId, ArrayList<Object> params){
|
|
| 929 |
params.add(resultid); |
|
| 930 |
String query = " DELETE FROM "+getTypeForTable(ClaimUtils.PROJECT)+" WHERE openaire_id = ? and NOT EXISTS \n" + |
|
| 931 |
" ( " +generateSelectOthersFromRelationTableQuery(resultid,claimId,getSourceTableName(ClaimUtils.PROJECT), params) + " union " + |
|
| 932 |
generateSelectOthersFromRelationTableQuery(resultid,claimId,getTargetTableName(ClaimUtils.PROJECT), params)+" ) ;\n" ; |
|
| 850 | 933 |
return query; |
| 851 | 934 |
} |
| 852 |
private String generateDeleteContextQuery(String resultid, String claimId ){
|
|
| 853 |
String query = " DELETE FROM "+getTypeForTable(ClaimUtils.CONTEXT)+" WHERE openaire_id = '"+resultid+"' and NOT EXISTS \n" + |
|
| 854 |
" ( " +generateSelectOthersFromRelationTableQuery(resultid,claimId,getSourceTableName(ClaimUtils.CONTEXT)) +" ) ;\n" ; |
|
| 935 |
private String generateDeleteContextQuery(String resultid, String claimId, ArrayList<Object> params){
|
|
| 936 |
params.add(resultid); |
|
| 937 |
String query = " DELETE FROM "+getTypeForTable(ClaimUtils.CONTEXT)+" WHERE openaire_id = ? and NOT EXISTS \n" + |
|
| 938 |
" ( " +generateSelectOthersFromRelationTableQuery(resultid,claimId,getSourceTableName(ClaimUtils.CONTEXT), params) +" ) ;\n" ; |
|
| 855 | 939 |
return query; |
| 856 | 940 |
} |
| 857 | 941 |
|
| ... | ... | |
| 863 | 947 |
* @param tableName |
| 864 | 948 |
* @return |
| 865 | 949 |
*/ |
| 866 |
private String generateSelectOthersFromRelationTableQuery(String resultid, String claimId , String tableName){
|
|
| 950 |
private String generateSelectOthersFromRelationTableQuery(String resultid, String claimId , String tableName, ArrayList<Object> params){
|
|
| 951 |
params.add(resultid); |
|
| 952 |
params.add(claimId); |
|
| 867 | 953 |
String query = |
| 868 |
" SELECT 1 FROM "+ tableName+" WHERE openaire_id = '"+resultid+"' and claim_id != " +claimId + " " ;
|
|
| 954 |
" SELECT 1 FROM "+ tableName+" WHERE openaire_id = ? and claim_id != ?::int " ;
|
|
| 869 | 955 |
return query; |
| 870 | 956 |
} |
| 871 |
public String generateDeleteFullClaimQuery(String id, String user, String sourceType, String sourceId, String targetType, String targetId){
|
|
| 957 |
public String generateDeleteFullClaimQuery(String id, String user, String sourceType, String sourceId, String targetType, String targetId, ArrayList<Object> params){
|
|
| 872 | 958 |
//TODO generate delete query |
| 959 |
params.add(id); |
|
| 960 |
params.add(id); |
|
| 961 |
params.add(id); |
|
| 873 | 962 |
String query = |
| 874 | 963 |
"BEGIN;\n" + |
| 875 | 964 |
//delete has_source_* |
| 876 |
"DELETE FROM "+ getSourceTableName(sourceType)+" WHERE claim_id = "+id+" ;\n" +
|
|
| 965 |
"DELETE FROM "+ getSourceTableName(sourceType)+" WHERE claim_id = ?::int ;\n" +
|
|
| 877 | 966 |
|
| 878 | 967 |
//delete has_target_* |
| 879 |
"DELETE FROM "+ getTargetTableName(targetType)+" WHERE claim_id = "+id+" ;\n" +
|
|
| 968 |
"DELETE FROM "+ getTargetTableName(targetType)+" WHERE claim_id = ?::int ;\n" +
|
|
| 880 | 969 |
|
| 881 | 970 |
//delete claim |
| 882 |
"DELETE FROM claim where id = "+id+";\n" +
|
|
| 971 |
"DELETE FROM claim where id = ?::int ;\n" +
|
|
| 883 | 972 |
|
| 884 | 973 |
//delete source |
| 885 |
generateDeleteEntityQuery(sourceId,id,sourceType)+ |
|
| 974 |
generateDeleteEntityQuery(sourceId,id,sourceType, params)+
|
|
| 886 | 975 |
|
| 887 | 976 |
//delete target |
| 888 |
generateDeleteEntityQuery(targetId,id,targetType)+ |
|
| 977 |
generateDeleteEntityQuery(targetId,id,targetType, params)+
|
|
| 889 | 978 |
"COMMIT"; |
| 890 | 979 |
return query; |
| 891 | 980 |
} |
| 892 |
public String generateSelectClaimQuery(String id, String user){
|
|
| 981 |
public String generateSelectClaimQuery(String id, String user, ArrayList<Object> params){
|
|
| 893 | 982 |
|
| 983 |
params.add(id); |
|
| 984 |
params.add(user); |
|
| 894 | 985 |
String query =" "; |
| 895 |
return " Select id, source_type, source_id, target_type, target_id from claim where id = "+id +" and claimedBy = '"+user+"' ";
|
|
| 986 |
return " Select id, source_type, source_id, target_type, target_id from claim where id = ?::int and claimedBy = ? ";
|
|
| 896 | 987 |
} |
| 897 |
public String generateSelectClaimQuery(String id){
|
|
| 988 |
public String generateSelectClaimQuery(String id, ArrayList<Object> params){
|
|
| 898 | 989 |
|
| 990 |
params.add(id); |
|
| 899 | 991 |
String query =" "; |
| 900 |
return " Select id, source_type, source_id, target_type, target_id,claimedBy from claim where id = "+id ;
|
|
| 992 |
return " Select id, source_type, source_id, target_type, target_id,claimedBy from claim where id = ?::int";
|
|
| 901 | 993 |
} |
| 902 | 994 |
/** |
| 903 | 995 |
* used to fetch the information from DB |
| ... | ... | |
| 905 | 997 |
* @param users |
| 906 | 998 |
* @return |
| 907 | 999 |
*/ |
| 908 |
public String generateSelectClaimQuery(List<String> ids, List<String> users){
|
|
| 1000 |
public String generateSelectClaimQuery(List<String> ids, List<String> users, ArrayList<Object> params){
|
|
| 909 | 1001 |
|
| 910 | 1002 |
String query =" Select id, source_type, source_id, target_type, target_id from claim where "; |
| 911 | 1003 |
if(ids.size()>0 && users.size() > 0 && ids.size() == users.size()) {
|
| 912 | 1004 |
for (int i = 0; i < ids.size(); i++) {
|
| 913 |
query+=" ( id = "+ids.get(i) +" and claimedBy = '"+users.get(i)+"' ) or"; |
|
| 1005 |
query+=" ( id = ? and claimedBy = ? ) or"; |
|
| 1006 |
params.add(ids.get(i)); |
|
| 1007 |
params.add(users.get(i)); |
|
| 914 | 1008 |
} |
| 915 | 1009 |
query = query.substring(0, query.length()-2); |
| 916 | 1010 |
return query; |
| ... | ... | |
| 918 | 1012 |
return null; |
| 919 | 1013 |
} |
| 920 | 1014 |
|
| 921 |
public String generateSelectProjectByIdQuery(String projectId) {
|
|
| 922 |
return " Select " + getProjectFields("project") + " from project where openaire_id = '"+projectId +"'";
|
|
| 1015 |
public String generateSelectProjectByIdQuery(String projectId, ArrayList<Object> params) {
|
|
| 1016 |
params.add(projectId); |
|
| 1017 |
return " Select " + getProjectFields("project") + " from project where openaire_id = ?";
|
|
| 923 | 1018 |
} |
| 924 | 1019 |
|
| 925 |
public String generateSelectContactEmailsByProjectIdQuery(String projectId) {
|
|
| 926 |
return " Select contact_person from project where openaire_id = '" + projectId + "'"; |
|
| 1020 |
public String generateSelectContactEmailsByProjectIdQuery(String projectId, ArrayList<Object> params) {
|
|
| 1021 |
params.add(projectId); |
|
| 1022 |
return " Select contact_person from project where openaire_id = ?"; |
|
| 927 | 1023 |
} |
| 928 | 1024 |
|
| 929 |
public String generateUpdateTokenByProjectId(String projectdId, String newToken) {
|
|
| 930 |
return "UPDATE project SET token = '" + newToken + "' WHERE openaire_id = '" + projectdId + "'"; |
|
| 1025 |
public String generateUpdateTokenByProjectId(String projectdId, String newToken, ArrayList<Object> params) {
|
|
| 1026 |
params.add(newToken); |
|
| 1027 |
params.add(projectdId); |
|
| 1028 |
return "UPDATE project SET token = ? WHERE openaire_id = ?"; |
|
| 931 | 1029 |
} |
| 932 |
public String generateUpdateContactEmailsByProjectIdByProjectId(String projectdId, List<String> contactEmails) {
|
|
| 1030 |
public String generateUpdateContactEmailsByProjectIdByProjectId(String projectdId, List<String> contactEmails, ArrayList<Object> params) {
|
|
| 933 | 1031 |
String emails = ""; |
| 934 | 1032 |
if(contactEmails != null && contactEmails.size() > 0){
|
| 935 |
emails+="'{";
|
|
| 1033 |
//emails+="'{";
|
|
| 936 | 1034 |
for(int i = 0; i< contactEmails.size(); i++){
|
| 937 | 1035 |
|
| 938 |
emails+="\""+contactEmails.get(i)+"\""+((i < contactEmails.size() -1 )?",":""); |
|
| 1036 |
emails+=contactEmails.get(i)+((i < contactEmails.size() -1 )?",":""); |
|
| 1037 |
//params.add(contactEmails.get(i)); |
|
| 939 | 1038 |
} |
| 940 |
emails+="}'"; |
|
| 1039 |
//emails+="}'";
|
|
| 941 | 1040 |
} |
| 942 | 1041 |
|
| 943 |
return "UPDATE project SET contact_person = " + emails + " WHERE openaire_id = '" + projectdId + "';"; |
|
| 1042 |
params.add(emails); |
|
| 1043 |
params.add(projectdId); |
|
| 1044 |
return "UPDATE project SET contact_person = string_to_array(?, ',') WHERE openaire_id = ?;"; |
|
| 944 | 1045 |
} |
| 945 | 1046 |
|
| 946 |
public String generateUpdateClaimCuration(String curatedBy, String claimId, boolean approved) {
|
|
| 1047 |
public String generateUpdateClaimCuration(String curatedBy, String claimId, boolean approved, ArrayList<Object> params) {
|
|
| 947 | 1048 |
SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
|
| 948 | 1049 |
Date date = new Date(); |
| 949 | 1050 |
String dateStr=null; |
| 950 | 1051 |
dateStr=(format.format(date)); |
| 951 | 1052 |
|
| 1053 |
params.add(dateStr); |
|
| 1054 |
params.add(curatedBy); |
|
| 1055 |
params.add(approved); |
|
| 1056 |
params.add(claimId); |
|
| 952 | 1057 |
String query = "UPDATE claim " |
| 953 |
+ "SET curation_date='" + dateStr + "', curated_by='" + curatedBy + "', approved='" + approved +"' "
|
|
| 954 |
+ "WHERE id = '" + claimId + "' RETURNING curation_date, curated_by, approved";
|
|
| 1058 |
+ "SET curation_date = ?::timestamp, curated_by = ?, approved = ? "
|
|
| 1059 |
+ "WHERE id = ?::int RETURNING curation_date, curated_by, approved";
|
|
| 955 | 1060 |
|
| 956 | 1061 |
logger.debug("Query to execute: "+query);
|
| 957 | 1062 |
|
Also available in: Unified diff
prepared statements for the sql queries