com.serphacker.serposcope.db.google.GoogleRankDB 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.google;
import com.google.inject.Singleton;
import com.mysema.commons.lang.CloseableIterator;
import com.querydsl.core.QueryFlag;
import com.querydsl.core.Tuple;
import com.querydsl.sql.SQLBindings;
import com.querydsl.sql.SQLQuery;
import com.querydsl.sql.dml.SQLDeleteClause;
import com.querydsl.sql.dml.SQLInsertClause;
import com.querydsl.sql.dml.SQLMergeBatch;
import com.querydsl.sql.dml.SQLMergeClause;
import com.serphacker.serposcope.db.AbstractDB;
import com.serphacker.serposcope.models.google.GoogleBest;
import com.serphacker.serposcope.models.google.GoogleRank;
import com.serphacker.serposcope.models.google.GoogleTarget;
import com.serphacker.serposcope.querybuilder.QGoogleRank;
import com.serphacker.serposcope.querybuilder.QGoogleRankBest;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collection;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.function.Consumer;
import java.util.regex.Pattern;
@Singleton
public class GoogleRankDB extends AbstractDB {
static QGoogleRank t_rank = QGoogleRank.googleRank;
static QGoogleRankBest t_best = QGoogleRankBest.googleRankBest;
public boolean insertBest(GoogleBest best){
boolean inserted = false;
try(Connection con = ds.getConnection()){
inserted = new SQLMergeClause(con, dbTplConf, t_best)
.set(t_best.groupId, best.getGroupId())
.set(t_best.googleTargetId, best.getGoogleTargetId())
.set(t_best.googleSearchId, best.getGoogleSearchId())
.set(t_best.rank, best.getRank())
.set(t_best.url, best.getUrl())
.set(t_best.runDay, best.getRunDay() == null ? null : Timestamp.valueOf(best.getRunDay()))
.execute() == 1;
} catch(Exception ex){
LOG.error("SQL error", ex);
}
return inserted;
}
public GoogleBest getBest(int groupId, int googleTargetId, int googleSearchId){
GoogleBest best = null;
try(Connection con = ds.getConnection()){
Tuple tuple = new SQLQuery(con, dbTplConf)
.select(t_best.all())
.from(t_best)
.where(t_best.groupId.eq(groupId))
.where(t_best.googleTargetId.eq(googleTargetId))
.where(t_best.googleSearchId.eq(googleSearchId))
.fetchOne();
if(tuple != null){
best = new GoogleBest(
tuple.get(t_best.groupId),
tuple.get(t_best.googleTargetId),
tuple.get(t_best.googleSearchId),
tuple.get(t_best.rank),
tuple.get(t_best.runDay) != null ? tuple.get(t_best.runDay).toLocalDateTime() : null,
tuple.get(t_best.url)
);
} else {
best = new GoogleBest(groupId, googleTargetId, googleSearchId, (short)GoogleRank.UNRANKED, null, null);
}
} catch(Exception ex){
LOG.error("SQL error", ex);
}
return best;
}
public boolean insert(GoogleRank rank) {
if(dbTplConf.getTemplates().isNativeMerge()){
return insertMerge(rank);
} else {
return insertOnDuplicateKey(rank);
}
}
public boolean insert(Collection rank) {
if(dbTplConf.getTemplates().isNativeMerge()){
return insertMerge(rank);
} else {
return insertOnDuplicateKey(rank);
}
}
public boolean insertMerge(Collection ranks){
try(Connection con = ds.getConnection()){
SQLMergeClause clause = new SQLMergeClause(con, dbTplConf, t_rank);
for (GoogleRank rank : ranks) {
clause
.set(t_rank.runId, rank.runId)
.set(t_rank.groupId, rank.groupId)
.set(t_rank.googleTargetId, rank.googleTargetId)
.set(t_rank.googleSearchId, rank.googleSearchId)
.set(t_rank.rank, rank.rank)
.set(t_rank.previousRank, rank.previousRank)
.set(t_rank.diff, rank.diff)
.set(t_rank.url, rank.url)
.addBatch();
}
return clause.execute() > 0;
} catch(Exception ex){
LOG.error("SQL error", ex);
}
return false;
}
public boolean insertOnDuplicateKey(Collection ranks){
try(Connection con = ds.getConnection()){
// waiting for patch https://github.com/querydsl/querydsl/issues/1921
/*
SQLInsertClause clause = new SQLInsertClause(con, dbTplConf, t_rank);
clause.setBatchToBulk(true);
for (GoogleRank rank : ranks) {
clause
.set(t_rank.runId, rank.runId)
.set(t_rank.groupId, rank.groupId)
.set(t_rank.googleTargetId, rank.googleTargetId)
.set(t_rank.googleSearchId, rank.googleSearchId)
.set(t_rank.rank, rank.rank)
.set(t_rank.previousRank, rank.previousRank)
.set(t_rank.diff, rank.diff)
.set(t_rank.url, rank.url)
.addBatch();
}
clause.addFlag(QueryFlag.Position.END,
" on duplicate key update rank = values(rank) " +
", previous_rank = values(previous_rank)" +
", diff = values(diff)" +
", url = values(url)"
);
return clause.execute() > 0;
*/
//
StringBuilder builder = new StringBuilder("INSERT INTO `GOOGLE_RANK` " +
"(`RUN_ID`, `GROUP_ID`, `GOOGLE_TARGET_ID`, `GOOGLE_SEARCH_ID`, `RANK`, `PREVIOUS_RANK`, `DIFF`, `URL`) " +
"VALUES ");
for (GoogleRank rank : ranks) {
builder.append("(");
builder.append(rank.runId).append(',');
builder.append(rank.groupId).append(',');
builder.append(rank.googleTargetId).append(',');
builder.append(rank.googleSearchId).append(',');
builder.append(rank.rank).append(',');
builder.append(rank.previousRank).append(',');
builder.append(rank.diff).append(',');
builder.append(dbTplConf.asLiteral(rank.url));
builder.append("),");
}
builder.setCharAt(builder.length()-1, ' ');
builder.append(" on duplicate key update rank = values(rank) " +
", previous_rank = values(previous_rank)" +
", diff = values(diff)" +
", url = values(url)"
);
try(Statement stmt = con.createStatement()){
return stmt.executeUpdate(builder.toString()) > 0;
}
} catch(Exception ex){
LOG.error("SQL error", ex);
}
return false;
}
public boolean insertMerge(GoogleRank rank) {
boolean inserted = false;
try(Connection con = ds.getConnection()){
inserted = new SQLMergeClause(con, dbTplConf, t_rank)
.set(t_rank.runId, rank.runId)
.set(t_rank.groupId, rank.groupId)
.set(t_rank.googleTargetId, rank.googleTargetId)
.set(t_rank.googleSearchId, rank.googleSearchId)
.set(t_rank.rank, rank.rank)
.set(t_rank.previousRank, rank.previousRank)
.set(t_rank.diff, rank.diff)
.set(t_rank.url, rank.url)
.execute() == 1;
} catch(Exception ex){
LOG.error("SQL error", ex);
}
return inserted;
}
public boolean insertOnDuplicateKey(GoogleRank rank) {
boolean inserted = false;
try(Connection con = ds.getConnection()){
inserted = new SQLInsertClause(con, dbTplConf, t_rank)
.set(t_rank.runId, rank.runId)
.set(t_rank.groupId, rank.groupId)
.set(t_rank.googleTargetId, rank.googleTargetId)
.set(t_rank.googleSearchId, rank.googleSearchId)
.set(t_rank.rank, rank.rank)
.set(t_rank.previousRank, rank.previousRank)
.set(t_rank.diff, rank.diff)
.set(t_rank.url, rank.url)
.addFlag(QueryFlag.Position.END,
" on duplicate key update rank = " + rank.rank +
", previous_rank = " + rank.previousRank +
", diff = " + rank.diff +
", url = " + (rank.url == null ? "NULL" : dbTplConf.asLiteral(rank.url))
)
.execute() == 1;
} catch(Exception ex){
LOG.error("SQL error", ex);
}
return inserted;
}
public int get(int runId, int groupId, int googleTargetId, int googleSearchId){
Short rank = null;
try(Connection con = ds.getConnection()){
rank = new SQLQuery(con, dbTplConf)
.select(t_rank.rank)
.from(t_rank)
.where(t_rank.runId.eq(runId))
.where(t_rank.groupId.eq(groupId))
.where(t_rank.googleTargetId.eq(googleTargetId))
.where(t_rank.googleSearchId.eq(googleSearchId))
.fetchOne();
} catch(Exception ex){
LOG.error("SQL error", ex);
}
return rank != null ? rank : GoogleRank.UNRANKED;
}
public GoogleRank getFull(int runId, int groupId, int googleTargetId, int googleSearchId){
GoogleRank rank = null;
try(Connection con = ds.getConnection()){
Tuple tuple = new SQLQuery(con, dbTplConf)
.select(t_rank.all())
.from(t_rank)
.where(t_rank.runId.eq(runId))
.where(t_rank.groupId.eq(groupId))
.where(t_rank.googleTargetId.eq(googleTargetId))
.where(t_rank.googleSearchId.eq(googleSearchId))
.fetchOne();
rank = fromTuple(tuple);
} catch(Exception ex){
LOG.error("SQL error", ex);
}
return rank;
}
public List list0(int runId, int groupId, int targetId){
List ranks = new ArrayList<>();
try(Connection con = ds.getConnection()){
List tuples = new SQLQuery(con, dbTplConf)
.select(t_rank.all())
.from(t_rank)
.where(t_rank.runId.eq(runId))
.where(t_rank.groupId.eq(groupId))
.where(t_rank.googleTargetId.eq(targetId))
.fetch();
for (Tuple tuple : tuples) {
ranks.add(fromTuple(tuple));
}
} catch(Exception ex){
LOG.error("SQL error", ex);
}
return ranks;
}
public List list(int runId, int groupId, int targetId){
return list(Arrays.asList(runId), Arrays.asList(groupId), Arrays.asList(targetId), null);
}
public List list(
Collection runs,
Collection groups,
Collection targets,
Collection searches
){
List ranks = new ArrayList<>();
try(Connection con = ds.getConnection()){
SQLQuery query = new SQLQuery(con, dbTplConf)
.select(t_rank.all())
.from(t_rank);
if(runs != null){
query.where(t_rank.runId.in(runs));
}
if(groups != null){
query.where(t_rank.groupId.in(groups));
}
if(targets != null){
query.where(t_rank.googleTargetId.in(targets));
}
if(searches != null){
query.where(t_rank.googleSearchId.in(searches));
}
List tuples = query.fetch();
for (Tuple tuple : tuples) {
ranks.add(fromTuple(tuple));
}
} catch(Exception ex){
LOG.error("SQL error", ex);
}
return ranks;
}
public boolean deleteBySearch(int groupId, int googleSearchId){
boolean deleted = false;
try(Connection con = ds.getConnection()){
deleted = new SQLDeleteClause(con, dbTplConf, t_best)
.where(t_best.groupId.eq(groupId))
.where(t_best.googleSearchId.eq(googleSearchId))
.execute() > 0;
deleted &= new SQLDeleteClause(con, dbTplConf, t_rank)
.where(t_rank.groupId.eq(groupId))
.where(t_rank.googleSearchId.eq(googleSearchId))
.execute() > 0;
} catch(Exception ex){
LOG.error("SQL error", ex);
}
return deleted;
}
public boolean deleteByTarget(int groupId, int googleTargetId){
boolean deleted = false;
try(Connection con = ds.getConnection()){
deleted = new SQLDeleteClause(con, dbTplConf, t_best)
.where(t_best.groupId.eq(groupId))
.where(t_best.googleTargetId.eq(googleTargetId))
.execute() > 0;
deleted &= new SQLDeleteClause(con, dbTplConf, t_rank)
.where(t_rank.groupId.eq(groupId))
.where(t_rank.googleTargetId.eq(googleTargetId))
.execute() > 0;
} catch(Exception ex){
LOG.error("SQL error", ex);
}
return deleted;
}
public void deleteByRunId(Integer runId){
try(Connection con = ds.getConnection()){
new SQLDeleteClause(con, dbTplConf, t_rank)
.where(t_rank.runId.eq(runId))
.execute();
} catch(Exception ex){
LOG.error("SQL error", ex);
}
}
public void wipe(){
try(Connection con = ds.getConnection()){
new SQLDeleteClause(con, dbTplConf, t_best).execute();
new SQLDeleteClause(con, dbTplConf, t_rank).execute();
} catch(Exception ex){
LOG.error("SQL error", ex);
}
}
public static GoogleRank fromTuple(Tuple tuple){
if(tuple == null){
return null;
}
return new GoogleRank(
tuple.get(t_rank.runId),
tuple.get(t_rank.groupId),
tuple.get(t_rank.googleTargetId),
tuple.get(t_rank.googleSearchId),
tuple.get(t_rank.rank),
tuple.get(t_rank.previousRank),
tuple.get(t_rank.url)
);
}
}
© 2015 - 2025 Weber Informatics LLC | Privacy Policy