Revision 58341
Added by Argiro Kokogiannaki about 4 years ago
| QueryGenerator.java | ||
|---|---|---|
| 200 | 200 |
" RETURNING openaire_id"; |
| 201 | 201 |
//TODO update on insert when exists |
| 202 | 202 |
} |
| 203 |
private String generateInsertClaimQuery(Date date, String claimedBy, String source_type, String target_type , String source_id , String target_id, String semantics, ArrayList<Object> params) {
|
|
| 203 |
private String generateInsertClaimQuery(Date date, String claimedBy, String source_type, String target_type , String source_id , String target_id, String claimedInDashboard, String semantics, ArrayList<Object> params) {
|
|
| 204 | 204 |
|
| 205 | 205 |
SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
|
| 206 | 206 |
String dateStr=null; |
| ... | ... | |
| 209 | 209 |
if(semantics!=null){
|
| 210 | 210 |
fields+=",semantics"; |
| 211 | 211 |
} |
| 212 |
if(claimedInDashboard!=null){
|
|
| 213 |
fields+=",claimed_in_dashboard"; |
|
| 214 |
} |
|
| 212 | 215 |
String values="?::timestamp,?,?,?,?,?"; |
| 213 | 216 |
params.add(dateStr); |
| 214 | 217 |
params.add(claimedBy); |
| ... | ... | |
| 220 | 223 |
values+=",?"; |
| 221 | 224 |
params.add(semantics); |
| 222 | 225 |
} |
| 223 |
|
|
| 226 |
if(claimedInDashboard!=null){
|
|
| 227 |
values+=",?"; |
|
| 228 |
params.add(claimedInDashboard); |
|
| 229 |
} |
|
| 224 | 230 |
return " INSERT INTO claim( "+fields+")\n" + |
| 225 | 231 |
" VALUES ( "+values+")\n" + |
| 226 | 232 |
" RETURNING id, source_id, target_id\n"; |
| ... | ... | |
| 240 | 246 |
" ),\n source AS (\n" + |
| 241 | 247 |
sourceQuery+ |
| 242 | 248 |
" ),\n myclaim AS (\n" + |
| 243 |
generateInsertClaimQuery(claim.getDate(), claim.getUserMail(), claim.getSourceType(), claim.getTargetType(), claim.getSource().getOpenaireId(), claim.getTarget().getOpenaireId(),this.createSemanticsType(claim.getSourceType(),claim.getTargetType()), params) + |
|
| 249 |
generateInsertClaimQuery(claim.getDate(), claim.getUserMail(), claim.getSourceType(), claim.getTargetType(), claim.getSource().getOpenaireId(), claim.getTarget().getOpenaireId(), claim.getClaimedInDashboard(), this.createSemanticsType(claim.getSourceType(),claim.getTargetType()), params) +
|
|
| 244 | 250 |
" ),\n ins4 AS (\n" + |
| 245 | 251 |
" INSERT INTO "+sourceTableName+" (claim_id, openaire_id)\n" + |
| 246 | 252 |
" SELECT id, source_id\n" + |
| ... | ... | |
| 302 | 308 |
" ),\n ins2 AS (\n" + |
| 303 | 309 |
sourceQuery+ |
| 304 | 310 |
" ),\n ins3 AS (\n" + |
| 305 |
generateInsertClaimQuery(claim.getDate(), claim.getUserMail(), claim.getSourceType(), claim.getTargetType(), claim.getSource().getOpenaireId(), claim.getTarget().getOpenaireId(),this.createSemanticsType(claim.getSourceType(),claim.getTargetType()), params) + |
|
| 311 |
generateInsertClaimQuery(claim.getDate(), claim.getUserMail(), claim.getSourceType(), claim.getTargetType(), claim.getSource().getOpenaireId(), claim.getTarget().getOpenaireId(), claim.getClaimedInDashboard(), this.createSemanticsType(claim.getSourceType(),claim.getTargetType()), params) +
|
|
| 306 | 312 |
" ),\n ins4 AS (\n" + |
| 307 | 313 |
" INSERT INTO has_source_result (claim_id, openaire_id)\n" + |
| 308 | 314 |
" SELECT id, source_id\n" + |
| ... | ... | |
| 469 | 475 |
" id serial primary key NOT NULL,\n" + |
| 470 | 476 |
" claim_date timestamp without time zone NOT NULL,\n" + |
| 471 | 477 |
" claimedBy text NOT NULL,\n" + |
| 478 |
" claimed_in_dashboard varchar(50),\n" + |
|
| 472 | 479 |
" source_type varchar(30) NOT NULL,\n" + |
| 473 | 480 |
" target_type varchar(30) NOT NULL,\n" + |
| 474 | 481 |
" source_id varchar(60) NOT NULL,\n" + |
| ... | ... | |
| 730 | 737 |
clauseParams.add(dateTo); |
| 731 | 738 |
return generateSelectClaimQueryAsUnionOfAllRelations(limit, offset, orderBy,desc,clause, clauseParams,keyword,types, params); |
| 732 | 739 |
} |
| 740 |
//used in CopyFromBetaToProduction |
|
| 741 |
public String generateFetchClaimsByDateForDashboards(String dateFrom, String dateTo, Integer limit, Integer offset,String keyword, String orderBy, boolean desc,List<String> types, ArrayList<Object> params, ArrayList<String> dashboards) {
|
|
| 742 |
String clause= " claim.claim_date >= ?::timestamp and claim.claim_date <= ?::timestamp "; |
|
| 743 |
ArrayList<Object> clauseParams = new ArrayList<>(); |
|
| 744 |
clauseParams.add(dateFrom); |
|
| 745 |
clauseParams.add(dateTo); |
|
| 746 |
if(dashboards.size()>0){
|
|
| 747 |
clause = clause.concat(" and (");
|
|
| 748 |
} |
|
| 749 |
for(int i =0; i < dashboards.size(); i++){
|
|
| 750 |
if(i > 0){
|
|
| 751 |
clause = clause.concat(" or ");
|
|
| 752 |
} |
|
| 753 |
clause = clause.concat(" claim.claimed_in_dashboard = ?");
|
|
| 754 |
clauseParams.add("beta_connect_"+dashboards.get(i));
|
|
| 755 |
} |
|
| 756 |
if(dashboards.size()>0){
|
|
| 757 |
clause = clause.concat(")");
|
|
| 758 |
} |
|
| 733 | 759 |
|
| 760 |
// logger.debug("\n\nClause:"+clause+"\n\n");
|
|
| 761 |
return generateSelectClaimQueryAsUnionOfAllRelations(limit, offset, orderBy,desc,clause, clauseParams,keyword,types, params); |
|
| 762 |
} |
|
| 763 |
|
|
| 734 | 764 |
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 | 765 |
String clause= " claim.claim_date >= ?::timestamp and claim.claim_date <= ?::timestamp and source.openaire_id =? "; |
| 736 | 766 |
ArrayList<Object> clauseParams = new ArrayList<>(); |
| ... | ... | |
| 916 | 946 |
String query = "select * from ("+
|
| 917 | 947 |
|
| 918 | 948 |
" ( " +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 |
" ( " +generateSelectclaimQuery(ClaimUtils.SOFTWARE,ClaimUtils.SOFTWARE,null,null,null,false, specificWhereClause, whereParams,keyword, params)+" ) \nunion "+ |
|
| 921 |
" ( " +generateSelectclaimQuery(ClaimUtils.OTHER,ClaimUtils.OTHER,null,null,null,false, specificWhereClause, whereParams,keyword, params)+" ) \nunion "+ |
|
| 949 |
// " ( " +generateSelectclaimQuery(ClaimUtils.DATASET,ClaimUtils.DATASET,null,null,null,false, specificWhereClause, whereParams,keyword, params)+" ) \nunion "+
|
|
| 950 |
// " ( " +generateSelectclaimQuery(ClaimUtils.SOFTWARE,ClaimUtils.SOFTWARE,null,null,null,false, specificWhereClause, whereParams,keyword, params)+" ) \nunion "+
|
|
| 951 |
// " ( " +generateSelectclaimQuery(ClaimUtils.OTHER,ClaimUtils.OTHER,null,null,null,false, specificWhereClause, whereParams,keyword, params)+" ) \nunion "+
|
|
| 922 | 952 |
" ( " +generateSelectclaimQuery(ClaimUtils.PROJECT,ClaimUtils.PUBLICATION, null,null,null,false, specificWhereClause, whereParams,keyword, params)+" ) \nunion "+ |
| 923 | 953 |
" ( " +generateSelectclaimQuery(ClaimUtils.CONTEXT,ClaimUtils.PUBLICATION,null,null,null,false, specificWhereClause, whereParams, keyword, params)+" )" + |
| 924 | 954 |
|
Also available in: Unified diff
ClaimEntity: Add information about the dashboard where the claim was created
Properties:
add properties for the different services
create beans properly
production or beta is decided from the services urls in properties
Migration:
create migration method: CopyFromBetaToProduction for communities