Revision 60411
Added by Argiro Kokogiannaki about 3 years ago
| modules/uoa-claims/trunk/src/test/java/eu/dnetlib/data/claims/CopyFromBetaTest.java | ||
|---|---|---|
| 46 | 46 |
@Test |
| 47 | 47 |
public void CopyFromBetaToProduction(){
|
| 48 | 48 |
log.setLevel(Level.ERROR); |
| 49 |
QueryGenerator q = new QueryGenerator(); |
|
| 50 |
System.out.println(q.generateCreateClaimTablesQuery()); |
|
| 51 | 49 |
try {
|
| 52 |
|
|
| 53 |
|
|
| 54 |
fetchClaimHandler.getQueryGenerator().setMigrationTable("claims_beta");
|
|
| 55 | 50 |
ArrayList<String> communities = new ArrayList<>(); |
| 56 |
communities.add("ee");
|
|
| 51 |
// 5 connect communities: 793 - done |
|
| 52 |
// communities.add("ee");
|
|
| 57 | 53 |
// communities.add("mes");
|
| 58 | 54 |
// communities.add("fam");
|
| 59 | 55 |
// communities.add("ni");
|
| 60 | 56 |
// communities.add("dh-ch");
|
| 57 |
|
|
| 58 |
//2nd group - 1073 - done |
|
| 59 |
//duplicates ids [20587 -22214] |
|
| 60 |
// select id, source_id, claimed_in_dashboard from claim where id>20586 and id < 22215 and (source_id like 'elixir-gr%' or source_id like 'enermaps%' or source_id like 'dariah%' or source_id like 'science-innovation-policy%' or source_id like 'oa-pg%'); |
|
| 61 |
// communities.add("elixir-gr");
|
|
| 62 |
// communities.add("enermaps");
|
|
| 63 |
// communities.add("dariah");
|
|
| 64 |
// communities.add("science-innovation-policy");
|
|
| 65 |
// communities.add("oa-pg");
|
|
| 66 |
|
|
| 67 |
// 3rd group - 555 - done |
|
| 68 |
// communities.add("egi");
|
|
| 69 |
// communities.add("rda");
|
|
| 70 |
|
|
| 71 |
|
|
| 72 |
// 4th group - 32673 - done |
|
| 61 | 73 |
// communities.add("covid-19");
|
| 62 | 74 |
String startDate = "2018-03-01"; |
| 63 |
String endDate = "2020-11-22";
|
|
| 75 |
String endDate = "2020-11-24";
|
|
| 64 | 76 |
|
| 65 |
fetchClaimHandler.getSqlDAO().getSqlStore().setDbUrl("jdbc:postgresql://scoobydoo.di.uoa.gr:5432/claims_beta");
|
|
| 77 |
//local |
|
| 78 |
fetchClaimHandler.getSqlDAO().getSqlStore().setDbUrl("jdbc:postgresql://localhost:5432/claims_beta");
|
|
| 79 |
//beta |
|
| 80 |
// fetchClaimHandler.getSqlDAO().getSqlStore().setDbUser("dnet");
|
|
| 81 |
// fetchClaimHandler.getSqlDAO().getSqlStore().setDbPassword("dnetPwd");
|
|
| 82 |
// fetchClaimHandler.getSqlDAO().getSqlStore().setDbUrl("jdbc:postgresql://beta.services.openaire.eu:5432/dnet_openaireplus");
|
|
| 66 | 83 |
List<Claim> claims = fetchClaimHandler.fetchClaimsByDateForDashboards(startDate,endDate, null,null,true, communities); |
| 67 | 84 |
System.out.println("Beta claims: ");
|
| 68 | 85 |
System.out.println(claims.size()); |
| 69 | 86 |
|
| 70 |
claimHandler.getSqlDAO().getSqlStore().setDbUrl("jdbc:postgresql://scoobydoo.di.uoa.gr:5432/movedb");
|
|
| 87 |
// fetchClaimHandler.getSqlDAO().getSqlStore().setDbUser("postgres");
|
|
| 88 |
// fetchClaimHandler.getSqlDAO().getSqlStore().setDbPassword("snowflakes");
|
|
| 89 |
// claimHandler.getSqlDAO().getSqlStore().setDbUrl("jdbc:postgresql://localhost:5432/movedb");
|
|
| 90 |
//prod |
|
| 91 |
claimHandler.getSqlDAO().getSqlStore().setDbUser("dnet");
|
|
| 92 |
claimHandler.getSqlDAO().getSqlStore().setDbPassword("dnetPwd");
|
|
| 93 |
claimHandler.getSqlDAO().getSqlStore().setDbUrl("jdbc:postgresql://postgresql.services.openaire.eu:5432/dnet_openaireplus");
|
|
| 94 |
/* out = new PrintWriter(new BufferedWriter(new FileWriter("/home/argirok/claims_migration/claims_migratedFromBeta_record_paths.txt", true)));
|
|
| 71 | 95 |
|
| 72 |
out = new PrintWriter(new BufferedWriter(new FileWriter("/home/argirok/claims_migratedFromBeta_record_paths.txt", true)));
|
|
| 73 |
|
|
| 74 | 96 |
for (Claim claim : claims) {
|
| 75 | 97 |
|
| 76 | 98 |
if(out!=null) {
|
| ... | ... | |
| 90 | 112 |
} |
| 91 | 113 |
if(out!=null) {
|
| 92 | 114 |
out.close(); |
| 93 |
} |
|
| 94 |
List<Claim> claimsmoveDB = fetchClaimHandler.fetchClaimsByDate(startDate,endDate, null, null, true);
|
|
| 95 |
System.out.println("Movedb claims:");
|
|
| 96 |
System.out.println(claimsmoveDB.size()); |
|
| 115 |
}*/
|
|
| 116 |
// List<Claim> claimsmoveDB = fetchClaimHandler.countAllClaims(startDate,endDate, null, null, true);
|
|
| 117 |
// System.out.println("Prod claims:");
|
|
| 118 |
// System.out.println(claimsmoveDB.size());
|
|
| 97 | 119 |
|
| 98 | 120 |
|
| 99 | 121 |
} catch (Exception | SQLStoreException e) {
|
| modules/uoa-claims/trunk/src/main/java/eu/dnetlib/data/claims/utils/QueryGenerator.java | ||
|---|---|---|
| 600 | 600 |
return " order by claim.claimedBy "+((desc)?"desc":"asc"); |
| 601 | 601 |
|
| 602 | 602 |
}else if(orderBy.equals("source")){
|
| 603 |
return " order by claim.source_title "+((desc)?"desc":"asc");
|
|
| 603 |
return " order by source_title "+((desc)?"desc":"asc"); |
|
| 604 | 604 |
|
| 605 | 605 |
}else if(orderBy.equals("target")){
|
| 606 |
return " order by claim.target_title "+((desc)?"desc":"asc");
|
|
| 606 |
return " order by target_title "+((desc)?"desc":"asc"); |
|
| 607 | 607 |
|
| 608 | 608 |
}else{
|
| 609 | 609 |
return " order by claim.claim_date desc"; |
| ... | ... | |
| 667 | 667 |
public String generateCountByContext(String contextId ,String keyword, List<String> types, ArrayList<Object> params) {
|
| 668 | 668 |
if((keyword == null || keyword.equals(""))&&types.isEmpty()){
|
| 669 | 669 |
params.add(contextId+"%"); |
| 670 |
return " select count(*) from claim where "+" claim.source_id like ?"; |
|
| 670 |
params.add("%_connect_" + contextId);
|
|
| 671 |
return " select count(*) from claim where "+" claim.source_id like ? or claim.claimed_in_dashboard like ? "; |
|
| 671 | 672 |
} |
| 672 | 673 |
return " select count(*) from claim where claim.id in ( select claim.id from ("+generateFetchClaimsByContext(contextId, null,null,keyword,null,false, types, params)+")as claim )";
|
| 673 | 674 |
|
| ... | ... | |
| 750 | 751 |
if(i > 0){
|
| 751 | 752 |
clause = clause.concat(" or ");
|
| 752 | 753 |
} |
| 753 |
clause = clause.concat(" claim.claimed_in_dashboard = ? or source_id = ?");
|
|
| 754 |
clause = clause.concat(" claim.claimed_in_dashboard = ? or source_id like ?");
|
|
| 754 | 755 |
clauseParams.add("beta_connect_"+dashboards.get(i));
|
| 755 |
clauseParams.add(dashboards.get(i)); |
|
| 756 |
clauseParams.add(dashboards.get(i)+"%");
|
|
| 756 | 757 |
} |
| 757 | 758 |
if(dashboards.size()>0){
|
| 758 | 759 |
clause = clause.concat(")");
|
| ... | ... | |
| 857 | 858 |
} |
| 858 | 859 |
|
| 859 | 860 |
private String getClaimFields() {
|
| 860 |
return " claim.id, claim.claim_date, claim.claimedBy, claim.source_type, claim.target_type, claim.semantics, claim.curation_date, claim.curated_by, claim.approved "; |
|
| 861 |
return " claim.id, claim.claim_date, claim.claimedBy, claim.source_type, claim.target_type, claim.semantics, claim.curation_date, claim.curated_by, claim.approved, claim.claimed_in_dashboard ";
|
|
| 861 | 862 |
} |
| 862 | 863 |
private String getResultFields(String tableAlias) {
|
| 863 | 864 |
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 "; |
| ... | ... | |
| 866 | 867 |
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 ";
|
| 867 | 868 |
} |
| 868 | 869 |
private String getContextFields(String tableAlias) {
|
| 869 |
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 "; |
|
| 870 |
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 ";
|
|
| 870 | 871 |
} |
| 871 | 872 |
|
| 872 | 873 |
private String getFieldsPerType(String type, String tableAlias){
|
| modules/uoa-claims/trunk/src/main/java/eu/dnetlib/data/claims/handler/FetchClaimHandler.java | ||
|---|---|---|
| 300 | 300 |
while(rs.next()) {
|
| 301 | 301 |
Claim claim= new Claim(); |
| 302 | 302 |
claims.add(claim); |
| 303 |
claim.setId(rs.getString(1)); |
|
| 303 |
Integer i = 1; |
|
| 304 |
claim.setId(rs.getString(i++)); |
|
| 304 | 305 |
DateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
|
| 305 |
Date date = format.parse(rs.getString(2));
|
|
| 306 |
Date date = format.parse(rs.getString(i++));
|
|
| 306 | 307 |
claim.setDate(date); |
| 307 |
claim.setUserMail(rs.getString(3));
|
|
| 308 |
claim.setSourceType(rs.getString(4));
|
|
| 309 |
claim.setTargetType(rs.getString(5));
|
|
| 310 |
claim.setSemantics(rs.getString(6));
|
|
| 311 |
|
|
| 308 |
claim.setUserMail(rs.getString(i++));
|
|
| 309 |
claim.setSourceType(rs.getString(i++));
|
|
| 310 |
claim.setTargetType(rs.getString(i++));
|
|
| 311 |
claim.setSemantics(rs.getString(i++));
|
|
| 312 |
log.debug(addCurationInfo); |
|
| 312 | 313 |
if(addCurationInfo) {
|
| 313 |
String curationDate =rs.getString(7);
|
|
| 314 |
String curationDate =rs.getString(i++);
|
|
| 314 | 315 |
if(curationDate != null) {
|
| 315 | 316 |
date = format.parse(curationDate); |
| 316 | 317 |
claim.setCurationDate(date); |
| 317 | 318 |
} |
| 318 |
claim.setCuratedBy(rs.getString(8)); |
|
| 319 |
claim.setCuratedBy(rs.getString(i++)); |
|
| 320 |
}else{
|
|
| 321 |
i+=2; |
|
| 319 | 322 |
} |
| 320 |
claim.setApproved(rs.getBoolean(9)); |
|
| 321 |
claim.setSource(buildEntity(rs,10,claim.getSourceType())); |
|
| 322 |
claim.setTarget(buildEntity(rs,22,claim.getTargetType())); |
|
| 323 |
log.debug(i); |
|
| 324 |
claim.setApproved(rs.getBoolean(i++)); |
|
| 325 |
claim.setClaimedInDashboard(rs.getString(i++)); |
|
| 326 |
claim.setSource(buildEntity(rs,i,claim.getSourceType())); |
|
| 327 |
claim.setTarget(buildEntity(rs,i+12,claim.getTargetType())); |
|
| 323 | 328 |
|
| 324 | 329 |
} |
| 325 | 330 |
return claims; |
Also available in: Unified diff
- fix order by titles
- context query: get results made in the same dashboard
- add claimed_in_dashboard in the result
- commit changes in Test CopyFromBeta