Revision 41157
Added by Argiro Kokogiannaki over 8 years ago
| QueryGenerator.java | ||
|---|---|---|
| 22 | 22 |
return "{call " + procedure + "()}";
|
| 23 | 23 |
} |
| 24 | 24 |
|
| 25 |
private String insertResult(String openaire_id, String result_type,String title, String collected_from , String external_url, String doi,String access_rights , String best_license,boolean found, String xml) {
|
|
| 25 |
private String insertResult(String openaire_id, String result_type,String title, String collected_from , String external_url, String doi,String access_rights ,String embargo_end_date, String best_license, String xml) {
|
|
| 26 | 26 |
if(title!=null&&title.contains("'")){
|
| 27 | 27 |
title=title.replace("'","''");
|
| 28 | 28 |
} |
| 29 |
String fields="openaire_id, found";
|
|
| 29 |
String fields="openaire_id"; |
|
| 30 | 30 |
if(result_type!=null){
|
| 31 | 31 |
fields+=", result_type"; |
| 32 | 32 |
} |
| ... | ... | |
| 45 | 45 |
if(access_rights!=null){
|
| 46 | 46 |
fields+=", access_rights"; |
| 47 | 47 |
} |
| 48 |
if(embargo_end_date!=null){
|
|
| 49 |
fields+=", embargo_end_date"; |
|
| 50 |
} |
|
| 48 | 51 |
if(best_license!=null){
|
| 49 | 52 |
fields+=", best_license"; |
| 50 | 53 |
} |
| 51 |
String values="'"+openaire_id+"','"+found+"'"; |
|
| 54 |
if(xml!=null){
|
|
| 55 |
fields+=", xml"; |
|
| 56 |
} |
|
| 57 |
String values="'"+openaire_id+"'"; |
|
| 52 | 58 |
if(result_type!=null){
|
| 53 | 59 |
values+=",'"+result_type+"'"; |
| 54 | 60 |
} |
| ... | ... | |
| 67 | 73 |
if(access_rights!=null){
|
| 68 | 74 |
values+=",'"+access_rights+"'"; |
| 69 | 75 |
} |
| 76 |
if(embargo_end_date!=null){
|
|
| 77 |
values+=",'"+embargo_end_date+"'"; |
|
| 78 |
} |
|
| 70 | 79 |
if(best_license!=null){
|
| 71 | 80 |
values+=",'"+best_license+"'"; |
| 72 | 81 |
} |
| 82 |
if(xml!=null){
|
|
| 83 |
values+=",'"+xml+"'"; |
|
| 84 |
} |
|
| 73 | 85 |
return "INSERT INTO result("+fields+")\n" +
|
| 74 | 86 |
" Select "+values+ |
| 75 | 87 |
" where not exists (select openaire_id from result where openaire_id='"+openaire_id+"')\n" + |
| 76 | 88 |
" RETURNING openaire_id"; |
| 77 | 89 |
} |
| 78 |
private String insertProject(String openaire_id, String name,String acronym, String funder_id,String funder_name, boolean found) {
|
|
| 90 |
private String insertProject(String openaire_id, String name,String acronym, String funder_id,String funder_name) {
|
|
| 79 | 91 |
if(name!=null) {
|
| 80 | 92 |
name = name.replace("'", "''");
|
| 81 | 93 |
} |
| ... | ... | |
| 92 | 104 |
if(funder_name!=null){
|
| 93 | 105 |
fields+=",funder_name"; |
| 94 | 106 |
} |
| 95 |
fields+=",found"; |
|
| 96 | 107 |
String values="'"+openaire_id+"'"; |
| 97 | 108 |
if(name!=null){
|
| 98 | 109 |
values+=",'"+name+"'"; |
| ... | ... | |
| 106 | 117 |
if(funder_name!=null){
|
| 107 | 118 |
values+=",'"+funder_name+"'"; |
| 108 | 119 |
} |
| 109 |
values+=",'"+found+"'"; |
|
| 110 | 120 |
return "INSERT INTO project("+fields+")\n" +
|
| 111 | 121 |
" Select "+values + |
| 112 | 122 |
" where not exists (select openaire_id from project where openaire_id='"+openaire_id+"')\n" + |
| ... | ... | |
| 128 | 138 |
} |
| 129 | 139 |
private String insertClaim(Date date, String claimedBy,String source_type, String target_type ,String source_id , String target_id) {
|
| 130 | 140 |
|
| 131 |
SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS");
|
|
| 141 |
SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
|
|
| 132 | 142 |
String dateStr=null; |
| 133 | 143 |
dateStr=(format.format(date)); |
| 134 | 144 |
|
| ... | ... | |
| 139 | 149 |
public String insertClaimhasResult(Result targetResult, Result sourceResult, Claim claim) {
|
| 140 | 150 |
|
| 141 | 151 |
return " WITH ins1 AS (\n" + |
| 142 |
insertResult(targetResult.getOpenaireId(),targetResult.getResultType(),targetResult.getTitle(),targetResult.getCollectedFrom(),targetResult.getExternal_url(),targetResult.getDoi(),targetResult.getAccessRights(), targetResult.getBestLicense(),targetResult.isFound(),targetResult.getXml())+
|
|
| 152 |
insertResult(targetResult.getOpenaireId(),targetResult.getResultType(),targetResult.getTitle(),targetResult.getCollectedFrom(),targetResult.getExternal_url(),targetResult.getDoi(),targetResult.getAccessRights(),targetResult.getEmbargoEndDate(), targetResult.getBestLicense(),targetResult.getXml())+
|
|
| 143 | 153 |
" ),\n ins2 AS (\n" + |
| 144 |
insertResult(sourceResult.getOpenaireId(),sourceResult.getResultType(),sourceResult.getTitle(),sourceResult.getCollectedFrom(),sourceResult.getExternal_url(),sourceResult.getDoi(),sourceResult.getAccessRights(), sourceResult.getBestLicense(),sourceResult.isFound(),sourceResult.getXml())+
|
|
| 154 |
insertResult(sourceResult.getOpenaireId(),sourceResult.getResultType(),sourceResult.getTitle(),sourceResult.getCollectedFrom(),sourceResult.getExternal_url(),sourceResult.getDoi(),sourceResult.getAccessRights(), targetResult.getEmbargoEndDate(), sourceResult.getBestLicense(),sourceResult.getXml())+
|
|
| 145 | 155 |
" ),\n ins3 AS (\n" + |
| 146 | 156 |
insertClaim(claim.getDate(),claim.getUserMail(),claim.getSourceType(),claim.getTargetType(),claim.getSource().getOpenaireId(),claim.getTarget().getOpenaireId()) + |
| 147 | 157 |
" ),\n ins4 AS (\n" + |
| ... | ... | |
| 155 | 165 |
public String insertClaimhasProject(Result targetResult, Project project, Claim claim) {
|
| 156 | 166 |
|
| 157 | 167 |
return " WITH ins1 AS (\n" + |
| 158 |
insertResult(targetResult.getOpenaireId(),targetResult.getResultType(),targetResult.getTitle(),targetResult.getCollectedFrom(),targetResult.getExternal_url(),targetResult.getDoi(),targetResult.getAccessRights(), targetResult.getBestLicense(),targetResult.isFound(), targetResult.getXml())+
|
|
| 168 |
insertResult(targetResult.getOpenaireId(),targetResult.getResultType(),targetResult.getTitle(),targetResult.getCollectedFrom(),targetResult.getExternal_url(),targetResult.getDoi(),targetResult.getAccessRights(), targetResult.getEmbargoEndDate(), targetResult.getBestLicense(), targetResult.getXml())+
|
|
| 159 | 169 |
" ),\n ins2 AS (\n" + |
| 160 |
insertProject(project.getOpenaireId(), project.getName(), project.getAcronym(), project.getFunderId(), project.getFunderName(), project.isFound())+
|
|
| 170 |
insertProject(project.getOpenaireId(), project.getName(), project.getAcronym(), project.getFunderId(), project.getFunderName())+ |
|
| 161 | 171 |
" ),\n ins3 AS (\n" + |
| 162 | 172 |
insertClaim(claim.getDate(),claim.getUserMail(),claim.getSourceType(),claim.getTargetType(),claim.getSource().getOpenaireId(),claim.getTarget().getOpenaireId()) + |
| 163 | 173 |
" ),\n ins4 AS (\n" + |
| ... | ... | |
| 171 | 181 |
public String insertClaimhasContext(Result targetResult, Context context, Claim claim) {
|
| 172 | 182 |
|
| 173 | 183 |
return " WITH ins1 AS (\n" + |
| 174 |
insertResult(targetResult.getOpenaireId(),targetResult.getResultType(),targetResult.getTitle(),targetResult.getCollectedFrom(),targetResult.getExternal_url(),targetResult.getDoi(),targetResult.getAccessRights(), targetResult.getBestLicense(),targetResult.isFound(), targetResult.getXml())+
|
|
| 184 |
insertResult(targetResult.getOpenaireId(),targetResult.getResultType(),targetResult.getTitle(),targetResult.getCollectedFrom(),targetResult.getExternal_url(),targetResult.getDoi(),targetResult.getAccessRights(), targetResult.getEmbargoEndDate(), targetResult.getBestLicense(), targetResult.getXml())+
|
|
| 175 | 185 |
" ),\n ins2 AS (\n" + |
| 176 | 186 |
insertContext(context.getOpenaireId(), context.getTitle())+ |
| 177 | 187 |
" ),\n ins3 AS (\n" + |
| ... | ... | |
| 204 | 214 |
" name text,\n" + |
| 205 | 215 |
" acronym varchar(60),\n" + |
| 206 | 216 |
" funder_id varchar(60),\n" + |
| 207 |
" funder_name varchar(60),\n" + |
|
| 208 |
" found boolean\n" + |
|
| 217 |
" funder_name varchar(60)\n" + |
|
| 209 | 218 |
");\n" + |
| 210 | 219 |
"\n" + |
| 211 | 220 |
"CREATE TABLE result (\n" + |
| 212 | 221 |
" openaire_id varchar(60) primary key NOT NULL,\n" + |
| 213 | 222 |
"\t\tresult_type varchar(30) NOT NULL,\n" + |
| 214 |
"\t\tdoi varchar(60),\n" +
|
|
| 223 |
"\t\tdoi text,\n" +
|
|
| 215 | 224 |
"\t\ttitle text,\n" + |
| 216 | 225 |
"\t\taccess_rights varchar(30),\n" + |
| 226 |
"\t\tembargo_end_date date,\n" + |
|
| 217 | 227 |
"\t\tbest_license varchar(30),\t\t\n" + |
| 218 | 228 |
"\t\texternal_url text,\n" + |
| 219 | 229 |
"\t\tcollected_from varchar(60) ,\n" + |
| 220 |
" found boolean,\n" + |
|
| 221 | 230 |
"\t\txml text\n" + |
| 222 | 231 |
");\n" + |
| 223 | 232 |
"\n" + |
| ... | ... | |
| 289 | 298 |
return " Select * from claims_view where type='rels2actions' and id>= "+startId+" and id <="+endId+ " order by id limit "+limit; |
| 290 | 299 |
|
| 291 | 300 |
} |
| 301 |
public String selectRelationClaimsWithResultidLike(String resultidLike, int limit) {
|
|
| 302 |
return " Select * from claims_view where type='rels2actions' and resultid like '%"+resultidLike+"%' limit " +limit ; |
|
| 303 |
|
|
| 304 |
} |
|
| 292 | 305 |
public String selectClaimById(String id) {
|
| 293 | 306 |
return " Select * from claims_view where id='"+id+"'"; |
| 294 | 307 |
|
Also available in: Unified diff
validation : needs project name OR acronym, create metadata file for crossref/ Datacite results - still working on Orcid, add embargo date field in Result, build Context (add name), when searching openaire API if result not found check with objIdentifier or search for dataset