com.serphacker.serposcope.db.base.RunDB Maven / Gradle / Ivy
/*
* Serposcope - SEO rank checker https://serposcope.serphacker.com/
*
* Copyright (c) 2016 SERP Hacker
* @author Pierre Nogues
* @license https://opensource.org/licenses/MIT MIT License
*/
package com.serphacker.serposcope.db.base;
import com.google.inject.Singleton;
import com.querydsl.core.Tuple;
import com.querydsl.sql.SQLQuery;
import com.querydsl.sql.dml.SQLDeleteClause;
import com.querydsl.sql.dml.SQLInsertClause;
import com.querydsl.sql.dml.SQLUpdateClause;
import com.serphacker.serposcope.db.AbstractDB;
import com.serphacker.serposcope.models.base.Group.Module;
import com.serphacker.serposcope.models.base.Run;
import com.serphacker.serposcope.models.base.Run.Status;
import static com.serphacker.serposcope.models.base.Run.Status.ABORTING;
import static com.serphacker.serposcope.models.base.Run.Status.DONE_ABORTED;
import static com.serphacker.serposcope.models.base.Run.Status.DONE_CRASHED;
import static com.serphacker.serposcope.models.base.Run.Status.DONE_SUCCESS;
import static com.serphacker.serposcope.models.base.Run.Status.DONE_WITH_ERROR;
import static com.serphacker.serposcope.models.base.Run.Status.RUNNING;
import com.serphacker.serposcope.querybuilder.QRun;
import java.sql.Connection;
import java.sql.Date;
import java.sql.Timestamp;
import java.time.LocalDate;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collection;
import java.util.Collections;
import java.util.List;
import java.util.stream.Collectors;
@Singleton
public class RunDB extends AbstractDB {
QRun t_run = QRun.run;
public int insert(Run run) {
int id = -1;
try(Connection conn = ds.getConnection()){
id = new SQLInsertClause(conn, dbTplConf, t_run)
.set(t_run.moduleId, run.getModule().ordinal())
.set(t_run.day, Date.valueOf(run.getDay() == null ? null : run.getDay()))
.set(t_run.started, run.getStarted()== null ? null : Timestamp.valueOf(run.getStarted()))
.set(t_run.finished, run.getFinished()== null ? null : Timestamp.valueOf(run.getFinished()))
.set(t_run.captchas, run.getCaptchas())
.set(t_run.progress, run.getProgress())
.set(t_run.errors, run.getErrors())
.set(t_run.status, run.getStatus().ordinal())
.set(t_run.mode, run.getMode().ordinal())
.executeWithKey(t_run.id);
run.setId(id);
}catch(Exception ex){
LOG.error("SQL error", ex);
}
return id;
}
public boolean updateStatus(Run run){
boolean updated = false;
try(Connection conn = ds.getConnection()){
updated = new SQLUpdateClause(conn, dbTplConf, t_run)
.set(t_run.status, run.getStatus().ordinal())
.where(t_run.id.eq(run.getId()))
.execute() == 1;
}catch(Exception ex){
LOG.error("SQL error", ex);
}
return updated;
}
public boolean updateStatusAborting(Run run){
boolean updated = false;
try(Connection conn = ds.getConnection()){
updated = new SQLUpdateClause(conn, dbTplConf, t_run)
.set(t_run.status, Status.ABORTING.ordinal())
.where(t_run.id.eq(run.getId()))
.where(t_run.status.eq(Status.RUNNING.ordinal()))
.execute() == 1;
}catch(Exception ex){
LOG.error("SQL error", ex);
}
return updated;
}
public boolean updateStarted(Run run){
boolean updated = false;
try(Connection conn = ds.getConnection()){
updated = new SQLUpdateClause(conn, dbTplConf, t_run)
.set(t_run.started, run.getStarted()== null ? null : Timestamp.valueOf(run.getStarted()))
.where(t_run.id.eq(run.getId()))
.execute() == 1;
}catch(Exception ex){
LOG.error("SQL error", ex);
}
return updated;
}
public boolean updateFinished(Run run){
boolean updated = false;
try(Connection conn = ds.getConnection()){
updated = new SQLUpdateClause(conn, dbTplConf, t_run)
.set(t_run.finished, run.getFinished() == null ? null : Timestamp.valueOf(run.getFinished()))
.set(t_run.progress, run.getProgress())
.set(t_run.captchas, run.getCaptchas())
.set(t_run.errors, run.getErrors())
.where(t_run.id.eq(run.getId()))
.execute() == 1;
}catch(Exception ex){
LOG.error("SQL error", ex);
}
return updated;
}
/**
* update progress, captchas, errors
*/
public boolean updateProgress(Run run){
boolean updated = false;
try(Connection conn = ds.getConnection()){
updated = new SQLUpdateClause(conn, dbTplConf, t_run)
.set(t_run.progress, run.getProgress())
.where(t_run.id.eq(run.getId()))
.execute() == 1;
}catch(Exception ex){
LOG.error("SQL error", ex);
}
return updated;
}
public boolean updateCaptchas(Run run){
boolean updated = false;
try(Connection conn = ds.getConnection()){
updated = new SQLUpdateClause(conn, dbTplConf, t_run)
.set(t_run.captchas, run.getCaptchas())
.where(t_run.id.eq(run.getId()))
.execute() == 1;
}catch(Exception ex){
LOG.error("SQL error", ex);
}
return updated;
}
public void delete(int runId){
try(Connection conn = ds.getConnection()){
new SQLDeleteClause(conn, dbTplConf, t_run).where(t_run.id.eq(runId)).execute();
}catch(Exception ex){
LOG.error("SQL error", ex);
}
}
public List listDone(Integer firstId, Integer lastId){
List runs = new ArrayList<>();
try(Connection conn = ds.getConnection()){
SQLQuery query = new SQLQuery<>(conn, dbTplConf)
.select(t_run.all())
.from(t_run);
if(firstId != null){
query = query.where(t_run.id.goe(firstId));
}
if(lastId != null){
query = query.where(t_run.id.loe(lastId));
}
List tuples = query
.where(t_run.finished.isNotNull())
.orderBy(t_run.id.asc())
.fetch();
for (Tuple tuple : tuples) {
Run run = fromTuple(tuple);
if(run != null){
runs.add(run);
}
}
}catch(Exception ex){
LOG.error("SQL error", ex);
}
return runs;
}
public long count(){
Long count = -1l;
try(Connection conn = ds.getConnection()){
count = new SQLQuery<>(conn, dbTplConf).select(t_run.count()).from(t_run).fetchFirst();
}catch(Exception ex){
LOG.error("SQL error", ex);
}
if(count == null){
count = -1l;
}
return count;
}
/*
public List listRunning(){
List runs = new ArrayList<>();
try(Connection conn = ds.getConnection()){
List tuples = new SQLQuery<>(conn, dbTplConf)
.select(t_run.all())
.from(t_run)
.where(t_run.status.eq(Run.Status.RUNNING.ordinal()))
.fetch();
for (Tuple tuple : tuples) {
runs.add(fromTuple(tuple));
}
}catch(Exception ex){
LOG.error("SQL error", ex);
}
return runs;
}
*/
public final static Collection STATUSES_RUNNING = Arrays.asList(RUNNING,ABORTING);
public final static Collection STATUSES_DONE = Arrays.asList(DONE_ABORTED,DONE_CRASHED,DONE_SUCCESS,DONE_WITH_ERROR);
public List listByStatus(Collection statuses, Long limit, Long offset){
List statusesVal = null;
if(statuses != null && !statuses.isEmpty()){
statusesVal = statuses.stream().map(Run.Status::ordinal).collect(Collectors.toList());
}
List runs = new ArrayList<>();
try(Connection conn = ds.getConnection()){
SQLQuery query = new SQLQuery<>(conn, dbTplConf)
.select(t_run.all())
.from(t_run);
if(statusesVal != null){
query.where(t_run.status.in(statusesVal));
}
query.orderBy(t_run.id.desc());
if(limit != null){
query.limit(limit);
}
if(offset != null){
query.offset(offset);
}
List tuples = query.fetch();
for (Tuple tuple : tuples) {
Run run = fromTuple(tuple);
if(run != null){
runs.add(run);
}
}
}catch(Exception ex){
LOG.error("SQL error", ex);
}
return runs;
}
public List findByDay(Module module, LocalDate day){
List runs = new ArrayList<>();
try(Connection conn = ds.getConnection()){
List tuples = new SQLQuery<>(conn, dbTplConf)
.select(t_run.all())
.from(t_run)
.where(t_run.moduleId.eq(module.ordinal()))
.where(t_run.day.eq(Date.valueOf(day)))
.fetch();
for (Tuple tuple : tuples) {
Run run = fromTuple(tuple);
if(run != null){
runs.add(run);
}
}
}catch(Exception ex){
LOG.error("SQL error", ex);
}
return runs;
}
public Run find(int runId){
Run run = null;
try(Connection conn = ds.getConnection()){
Tuple tuple = new SQLQuery<>(conn, dbTplConf)
.select(t_run.all())
.from(t_run)
.where(t_run.id.eq(runId))
.fetchFirst();
run = fromTuple(tuple);
}catch(Exception ex){
LOG.error("SQL error", ex);
}
return run;
}
public Run findPrevious(int runId){
Run run = null;
try(Connection conn = ds.getConnection()){
Tuple tuple = new SQLQuery<>(conn, dbTplConf)
.select(t_run.all())
.from(t_run)
.where(t_run.id.lt(runId))
.orderBy(t_run.id.desc())
.fetchFirst();
run = fromTuple(tuple);
}catch(Exception ex){
LOG.error("SQL error", ex);
}
return run;
}
public Run findLast(Module module, Collection statuses, LocalDate untilDate){
Run run = null;
try(Connection conn = ds.getConnection()){
SQLQuery query = new SQLQuery<>(conn,dbTplConf)
.select(t_run.all())
.from(t_run)
.where(t_run.moduleId.eq(module.ordinal()));
if( statuses != null){
query.where(t_run.status.in(statuses.stream().map(Run.Status::ordinal).collect(Collectors.toList())));
}
if(untilDate != null){
query.where(t_run.day.loe(Date.valueOf(untilDate)));
}
Tuple tuple = query
.orderBy(t_run.id.desc())
.fetchFirst();
run = fromTuple(tuple);
}catch(Exception ex){
LOG.error("SQL error", ex);
}
return run;
}
public Run findFirst(Module module, Collection statuses, LocalDate fromDate){
Run run = null;
try(Connection conn = ds.getConnection()){
SQLQuery query = new SQLQuery<>(conn,dbTplConf)
.select(t_run.all())
.from(t_run)
.where(t_run.moduleId.eq(module.ordinal()));
if( statuses != null){
query.where(t_run.status.in(statuses.stream().map(Run.Status::ordinal).collect(Collectors.toList())));
}
if(fromDate != null){
query.where(t_run.day.goe(Date.valueOf(fromDate)));
}
Tuple tuple = query
.orderBy(t_run.id.asc())
.fetchFirst();
run = fromTuple(tuple);
}catch(Exception ex){
LOG.error("SQL error", ex);
}
return run;
}
public void wipe(){
try(Connection con = ds.getConnection()){
new SQLDeleteClause(con, dbTplConf, t_run).execute();
} catch(Exception ex){
LOG.error("SQL error", ex);
}
}
protected Run fromTuple(Tuple tuple){
if(tuple == null){
return null;
}
Run run = new Run();
run.setId(tuple.get(t_run.id));
run.setModule(Module.values()[tuple.get(t_run.moduleId)]);
run.setDay(tuple.get(t_run.day) == null ? null : tuple.get(t_run.day).toLocalDate());
run.setStarted(tuple.get(t_run.started) == null ? null : tuple.get(t_run.started).toLocalDateTime());
run.setFinished(tuple.get(t_run.finished) == null ? null : tuple.get(t_run.finished).toLocalDateTime());
run.setStatus(Run.Status.values()[tuple.get(t_run.status)]);
run.setProgress(tuple.get(t_run.progress));
run.setErrors(tuple.get(t_run.errors));
run.setCaptchas(tuple.get(t_run.captchas));
run.setMode(Run.Mode.values()[tuple.get(t_run.mode)]);
return run;
}
}
© 2015 - 2025 Weber Informatics LLC | Privacy Policy