package fullOne;
import org.blackdread.filtersortjooqapi.filter.Filter;
import org.blackdread.filtersortjooqapi.filter.FilterValue;
import org.blackdread.filtersortjooqapi.filter.parser.FilterMultipleValueParser;
import org.blackdread.filtersortjooqapi.filter.parser.FilterParser;
import org.blackdread.filtersortjooqapi.sort.SortValue;
import org.jooq.*;
import org.jooq.impl.DSL;
import org.jooq.impl.SQLDataType;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;
import javax.annotation.PostConstruct;
import javax.validation.constraints.NotNull;
import java.sql.Timestamp;
import java.time.Duration;
import java.time.ZonedDateTime;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;
import java.util.Map;
import static com.example.config.Constants.DATABASE_TIMEZONE;
import static com.example.jooq.tables.Atable.ATABLE;
import static com.example.jooq.tables.File.FILE;
import static com.example.jooq.tables.FileTransitionHistory.FILE_TRANSITION_HISTORY;
import static com.example.jooq.tables.FileType.FILE_TYPE;
import static com.example.jooq.tables.TransitionState.TRANSITION_STATE;
import static com.example.jooq.tables.User.USER;
import static com.example.jooq.tables.Visual.VISUAL;
import static com.example.jooq.tables.VisualHasSomething.VISUAL_HAS_SOMETHING;
import static org.jooq.impl.DSL.*;
@Repository
@Transactional(readOnly = true)
public class TransferTimingPageRepositoryImplPureJooq implements TransferTimingPageRepository {
private final Logger log = LoggerFactory.getLogger(TransferTimingPageRepositoryImplPureJooq.class);
private final DSLContext create;
/**
* Define the index table
*/
private static final Table<Record> TRANSFER_TIMING = DSL.table(DSL.name("_transfer_timing"));
private static final Field<Long> TRANSFER_TIMING_FILE_TRANSITION_HISTORY_ID = field(name(TRANSFER_TIMING.getName(), "file_transition_history_id"), SQLDataType.BIGINT.nullable(false));
private static final Field<Integer> TRANSFER_TIMING_VISUAL_COUNT = field(name(TRANSFER_TIMING.getName(), "visual_count"), SQLDataType.INTEGER);
private static final Field<Integer> TRANSFER_TIMING_SOMETHING_COUNT = field(name(TRANSFER_TIMING.getName(), "something_count"), SQLDataType.INTEGER);
private static final Field<Integer> TRANSFER_TIMING_TRANSITION_STATE_FROM_ID = field(name(TRANSFER_TIMING.getName(), "transition_state_from_id"), SQLDataType.INTEGER);
private static final Field<Integer> TRANSFER_TIMING_TRANSITION_STATE_TO_ID = field(name(TRANSFER_TIMING.getName(), "transition_state_to_id"), SQLDataType.INTEGER);
private static final Field<Byte> TRANSFER_TIMING_TRANSFER_STATUS = field(name(TRANSFER_TIMING.getName(), "transfer_status"), SQLDataType.TINYINT);
private static final Field<Timestamp> TRANSFER_TIMING_TRANSFER_STARTED_AT = field(name(TRANSFER_TIMING.getName(), "transfer_started_at"), SQLDataType.TIMESTAMP);
private static final Field<Timestamp> TRANSFER_TIMING_TRANSFER_ENDED_AT = field(name(TRANSFER_TIMING.getName(), "transfer_ended_at"), SQLDataType.TIMESTAMP);
private static final Field<Long> TRANSFER_TIMING_TRANSFER_TIME_SPENT_IN_SECONDS = field(name(TRANSFER_TIMING.getName(), "transfer_time_spent_in_seconds"), SQLDataType.BIGINT);
@Autowired
public TransferTimingPageRepositoryImplPureJooq(DSLContext create) {
this.create = create;
}
private Collection<SortField<?>> buildSort(final Sort sortSpecification){
Collection<SortField<?>> querySortFields = new ArrayList<>(10);
if (sortSpecification == null) {
// What is the default sorting?
// Consistent way to write it in all app?
return querySortFields;
}
List<String> usedSortKey = new ArrayList<>(10);
for (final Sort.Order specifiedField : sortSpecification) {
String sortFieldName = specifiedField.getProperty();
Sort.Direction sortDirection = specifiedField.getDirection();
if (usedSortKey.contains(sortFieldName))
throw new IllegalArgumentException("Well how should it be handled? And should be same for all app to be consistent");
querySortFields.add(getSortedField(sortFieldName, sortDirection));
usedSortKey.add(sortFieldName);
}
return querySortFields;
}
private SortField<?> getSortedField(final String sortFieldName, final Sort.Direction sortDirection){
SortOrder sortOrder;
if (sortDirection == Sort.Direction.ASC) {
sortOrder = SortOrder.ASC;
} else {
sortOrder = SortOrder.DESC;
}
switch (sortFieldName){
case "id":
return TRANSFER_TIMING_FILE_TRANSITION_HISTORY_ID.sort(sortOrder);
case "atableName":
return ATABLE.NAME.sort(sortOrder);
case "username":
return USER.USERNAME.sort(sortOrder);
case "blablabla......... etc":
// Etc for others
return null;
default:
throw new IllegalArgumentException("Well how should it be handled? And should be same for all app to be consistent");
}
}
private Condition buildConditions(final Map<String, String> requestParams){
final List<Condition> conditions = new ArrayList<>(requestParams.size());
conditions.add(DSL.trueCondition());
if(requestParams.containsKey("code")){
conditions.add(FILE.CODE.likeIgnoreCase("%" + requestParams.get("code") + "%"));
}
if(requestParams.containsKey("username")){
conditions.add(USER.USERNAME.likeIgnoreCase("%" + requestParams.get("username") + "%"));
}
if(requestParams.containsKey("atableName")){
conditions.add(ATABLE.NAME.likeIgnoreCase("%" + requestParams.get("atableName") + "%"));
}
if(requestParams.containsKey("transferStatus")){
// Ok so we use valueOf so we will get NumberFormatException if the value is not good -> which might result in html 500 internal server error in web environment
conditions.add(TRANSFER_TIMING_TRANSFER_STATUS.eq(Byte.valueOf(requestParams.get("transferStatus"))));
}
// What about the case the date range is specified like (need split, parse): key=value -> transferTimingRange=date1,date2
// Here what about checking if only one present and not the other, should throw an exception
if(requestParams.containsKey("transferStartedAt") && requestParams.containsKey("transferEndedAt")){
// again parse the LocalDate
// handle or not exception
// add the condition
}
// Etc
// Do if/else
// parse values
// throw exceptions (but which one, will it stay consistent through all app?)
// Keep consistent through all your app
// Copy/paste to try to keep consistent
// ===========
// Here what about other keys that were not matched to a if? We just ignore? What about having a
// consistent REST API where you expect to send data based on a consistent client behavior to pass
// only required fields amd all fields passed should have meaning and used.
// So no ignore and un-expected behavior, so here we should do even more tests with some more if/else
// ===========
return DSL.and(conditions);
}
/**
* Get listing
*
* @param pageable the pagination information
* @return the list of entities
*/
@Override
@Transactional(readOnly = true)
public Page<TransferTimingListingDTO> getAll(Pageable pageable) {
return new PageImpl<>(basicSelect(pageable), pageable, getCountMaxElements());
}
/**
* Get filtered listing
*
* @param requestParams the queries of the search
* @param pageable the pagination information
* @return the list of entities
*/
@Override
@Transactional(readOnly = true)
public Page<TransferTimingListingDTO> filter(Map<String, String> requestParams, Pageable pageable) {
return new PageImpl<>(basicSelectFilter(pageable, requestParams), pageable, getCountMaxElements(requestParams));
}
/**
* Get listing without paging
*
* @param pageable the pagination information
* @return the list of entities
*/
@Override
@Transactional(readOnly = true)
public List<TransferTimingListingDTO> getAllWithoutPaging(Pageable pageable) {
return basicSelectWithoutPaging(pageable);
}
/**
* Get filtered listing without paging
*
* @param requestParams the queries of the search
* @param pageable the pagination information
* @return the list of entities
*/
@Override
@Transactional(readOnly = true)
public List<TransferTimingListingDTO> filterWithoutPaging(Map<String, String> requestParams, Pageable pageable) {
return basicSelectFilterWithoutPaging(pageable, requestParams);
}
/**
* Get count
*
* @return total count
*/
private int getCountMaxElements() {
return buildQuery(basicSelectCount()).fetchOne(0, int.class);
}
/**
* Get count from filtered result
*
* @param requestParams the queries of the search
* @return total count
*/
private int getCountMaxElements(Map<String, String> requestParams) {
return buildQuery(basicSelectCount())
.where(buildConditions(requestParams))
.fetchOne(0, int.class);
}
/**
* Build full select query
*
* @param pageable the pagination information
* @return list of dtos
*/
private List<TransferTimingListingDTO> basicSelect(Pageable pageable) {
return buildQuery(basicSelectQuery())
.orderBy(buildSort(pageable.getSort()))
.limit(pageable.getOffset(), pageable.getPageSize())
.fetch()
.map(TransferTimingPageRepositoryImpl::recordToDto);
}
/**
* Build full select query with filter
*
* @param pageable the pagination information
* @param requestParams the queries of the search
* @return list of dtos
*/
private List<TransferTimingListingDTO> basicSelectFilter(Pageable pageable, Map<String, String> requestParams) {
return buildQuery(basicSelectQuery())
.and(buildConditions(requestParams))
.orderBy(buildSort(pageable.getSort()))
.limit(pageable.getOffset(), pageable.getPageSize())
.fetch()
.map(TransferTimingPageRepositoryImpl::recordToDto);
}
/**
* Build full select query without paging
*
* @param pageable the pagination information
* @return list of dtos
*/
private List<TransferTimingListingDTO> basicSelectWithoutPaging(Pageable pageable) {
return buildQuery(basicSelectQuery())
.orderBy(buildSort(pageable.getSort()))
.fetch()
.map(TransferTimingPageRepositoryImpl::recordToDto);
}
/**
* Build full select query with filter without paging
*
* @param pageable the pagination information
* @param requestParams the queries of the search
* @return list of dtos
*/
private List<TransferTimingListingDTO> basicSelectFilterWithoutPaging(Pageable pageable, Map<String, String> requestParams) {
return buildQuery(basicSelectQuery())
.and(buildConditions(requestParams))
.orderBy(buildSort(pageable.getSort()))
.fetch()
.map(TransferTimingPageRepositoryImpl::recordToDto);
}
/**
* Build select count query
*
* @return total count
*/
private SelectSelectStep<Record1<Integer>> basicSelectCount() {
return create.select(count());
}
/**
* Build select query
*
* @return selected records
*/
private SelectSelectStep<Record16<Long, Integer, Integer, String, String, String, String, Integer, Integer, String, String, String, Byte, Timestamp, Timestamp, Long>> basicSelectQuery() {
return create.select(
TRANSFER_TIMING_FILE_TRANSITION_HISTORY_ID,
FILE.ID.as("fileId"),
ATABLE.ID.as("atableId"),
ATABLE.NAME.as("atableName"),
USER.USERNAME,
FILE.CODE,
FILE_TYPE.NAME.as("fileTypeName"),
TRANSFER_TIMING_VISUAL_COUNT,
TRANSFER_TIMING_SOMETHING_COUNT,
field(name("tableFromState", TRANSITION_STATE.NAME.getName()), SQLDataType.VARCHAR(50)).as("fromStatus"),
field(name("tableToState", TRANSITION_STATE.NAME.getName()), SQLDataType.VARCHAR(50)).as("toStatus"),
FILE_TRANSITION_HISTORY.LOCATION_IPV4,
TRANSFER_TIMING_TRANSFER_STATUS,
TRANSFER_TIMING_TRANSFER_STARTED_AT,
TRANSFER_TIMING_TRANSFER_ENDED_AT,
TRANSFER_TIMING_TRANSFER_TIME_SPENT_IN_SECONDS
);
}
/**
* Build query with select and table
*
* @param selectedField select field(s)
* @param <T> record type
* @return built query
*/
private <T extends Record> SelectOnConditionStep<T> buildQuery(final SelectSelectStep<T> selectedField) {
return selectedField
.from(TRANSFER_TIMING)
.join(FILE_TRANSITION_HISTORY).on(FILE_TRANSITION_HISTORY.ID.eq(field(name(TRANSFER_TIMING.getName(), TRANSFER_TIMING_FILE_TRANSITION_HISTORY_ID.getName()), SQLDataType.INTEGER)))
.join(ATABLE).on(ATABLE.ID.eq(FILE_TRANSITION_HISTORY.FILE_LISTING_ATABLE_ID))
.join(USER).on(USER.ID.eq(FILE_TRANSITION_HISTORY.LOCATION_USER_ID))
.join(TRANSITION_STATE.asTable("tableFromState")).on(field(name("tableFromState", TRANSITION_STATE.ID.getName())).eq(field(name(TRANSFER_TIMING.getName(), TRANSFER_TIMING_TRANSITION_STATE_FROM_ID.getName()), SQLDataType.INTEGER)))
.join(TRANSITION_STATE.asTable("tableToState")).on(field(name("tableToState", TRANSITION_STATE.ID.getName())).eq(field(name(TRANSFER_TIMING.getName(), TRANSFER_TIMING_TRANSITION_STATE_TO_ID.getName()), SQLDataType.INTEGER)))
.join(FILE).on(FILE.ID.eq(FILE_TRANSITION_HISTORY.FILE_ID))
.join(FILE_TYPE).on(FILE_TYPE.ID.eq(FILE.FILE_TYPE_ID));
}
private static TransferTimingListingDTO recordToDto(Record16<Long, Integer, Integer, String, String, String, String, Integer, Integer, String, String, String, Byte, Timestamp, Timestamp, Long> r) {
// You can do your mapping manually or use jooq methods like "into" etc
return new TransferTimingListingDTO(
r.get(TRANSFER_TIMING_FILE_TRANSITION_HISTORY_ID, Long.class),
r.get("fileId", Long.class),
r.get("atableId", Long.class),
r.get("atableName", String.class),
r.get(USER.USERNAME, String.class),
r.get(FILE.CODE, String.class),
r.get("fileTypeName", String.class),
r.get(TRANSFER_TIMING_VISUAL_COUNT, Integer.class),
r.get(TRANSFER_TIMING_SOMETHING_COUNT, Integer.class),
r.get(field("fromStatus"), String.class),
r.get(field("toStatus"), String.class),
r.get(FILE_TRANSITION_HISTORY.LOCATION_IPV4, String.class),
r.get(TRANSFER_TIMING_TRANSFER_STATUS, Boolean.class),
// Set zone to specific one if you are not using UTC (you should)
r.get(TRANSFER_TIMING_TRANSFER_STARTED_AT).toLocalDateTime().atZone(DATABASE_TIMEZONE),
r.get(TRANSFER_TIMING_TRANSFER_ENDED_AT).toLocalDateTime().atZone(DATABASE_TIMEZONE),
r.get(TRANSFER_TIMING_TRANSFER_TIME_SPENT_IN_SECONDS, Long.class)
);
}
/**
* Create index
*/
@Transactional(readOnly = false)
public void createIndex() {
buildIndex();
}
/**
* Force create index
*
* @param force true for force index
*/
@Transactional(readOnly = false)
public void createIndex(Boolean force) {
if (force.equals(true)) {
create.dropTableIfExists(TRANSFER_TIMING).execute();
}
buildIndex();
}
/**
* Build index
*/
private void buildIndex() {
int tableExist =
create.select(count())
.from("information_schema.tables")
.where("table_schema = '" + FILE.getSchema().getName() + "'")
.and("table_name = '" + TRANSFER_TIMING.getName() + "'")
.fetchOne(0, int.class);
if (tableExist == 0) {
createIndexTable();
}
final Select<Record1<Long>> indexedIds = create.select(
TRANSFER_TIMING_FILE_TRANSITION_HISTORY_ID
).from(TRANSFER_TIMING);
// select and compute records
final Field<Integer> visualCount = count(VISUAL.ID).as("visualCount");
final Table<Record2<Integer, Integer>> tableVisualCount = create.select(
visualCount,
VISUAL.FILE_ID
).from(VISUAL)
.groupBy(VISUAL.FILE_ID)
.asTable("tableVisualCount");
final Field<Integer> somethingCount = count().as("somethingCount");
final Table<Record2<Integer, Integer>> tablesomethingCount = create.select(
somethingCount,
VISUAL_HAS_SOMETHING.VISUAL_FILE_ID
).from(VISUAL_HAS_SOMETHING)
.groupBy(VISUAL_HAS_SOMETHING.VISUAL_FILE_ID)
.asTable("tablesomethingCount");
SelectOnConditionStep<Record6<Integer, Integer, Integer, Integer, Integer, Timestamp>> query = create.select(
FILE_TRANSITION_HISTORY.ID,
FILE_TRANSITION_HISTORY.FILE_ID,
tableVisualCount.field(visualCount.getName(), SQLDataType.INTEGER),
tablesomethingCount.field(somethingCount.getName(), SQLDataType.INTEGER),
FILE_TRANSITION_HISTORY.TRANSITION_STATE_ID,
FILE_TRANSITION_HISTORY.CREATE_TIME
).from(FILE_TRANSITION_HISTORY)
.join(USER).on(FILE_TRANSITION_HISTORY.LOCATION_USER_ID.eq(USER.ID))
.join(TRANSITION_STATE).on(FILE_TRANSITION_HISTORY.TRANSITION_STATE_ID.eq(TRANSITION_STATE.ID))
.join(FILE).on(FILE_TRANSITION_HISTORY.FILE_ID.eq(FILE.ID))
.join(FILE_TYPE).on(FILE.FILE_TYPE_ID.eq(FILE_TYPE.ID))
.join(ATABLE).on(FILE_TRANSITION_HISTORY.FILE_LISTING_ATABLE_ID.eq(ATABLE.ID))
.leftJoin(tableVisualCount).on(FILE_TRANSITION_HISTORY.FILE_ID.eq(tableVisualCount.field(VISUAL.FILE_ID)))
.leftJoin(tablesomethingCount).on(FILE_TRANSITION_HISTORY.FILE_ID.eq(tablesomethingCount.field(VISUAL_HAS_SOMETHING.VISUAL_FILE_ID)));
query.where(field(name(FILE_TRANSITION_HISTORY.getName(), FILE_TRANSITION_HISTORY.ID.getName()), SQLDataType.BIGINT).notIn(indexedIds));
query.orderBy(FILE_TRANSITION_HISTORY.FILE_ID.asc(), FILE_TRANSITION_HISTORY.CREATE_TIME.asc(), FILE_TRANSITION_HISTORY.ID.asc());
Result<Record6<Integer, Integer, Integer, Integer, Integer, Timestamp>> result = query.fetch();
Long fileIdTemp = 0L;
// Here are possible status (usually you use an Enum)
Integer statusTransferring = 2;
Integer statusTransferred = 3;
Integer statusError = 1;
ZonedDateTime transferStartedAt = null;
ZonedDateTime transferEndedAt = null;
Integer transitionStateFromId = null;
Integer transitionStateToId = null;
Boolean transferStatus = false;
Long durationInSeconds = 0L;
List<TransferTimingIndexDTO> transferTimingIndexDTOs = new ArrayList<>();
boolean gotTransferring = false;
for (Record r : result) {
Long fileTransitionHistoryId = r.get(FILE_TRANSITION_HISTORY.ID, Long.class);
Long fileId = r.get(FILE_TRANSITION_HISTORY.FILE_ID, Long.class);
Integer visualCountValue = r.get(visualCount, Integer.class);
Integer somethingCountValue = r.get(somethingCount, Integer.class);
Integer transitionStateId = r.get(FILE_TRANSITION_HISTORY.TRANSITION_STATE_ID, Integer.class);
ZonedDateTime createTime = r.get(FILE_TRANSITION_HISTORY.CREATE_TIME).toLocalDateTime().atZone(DATABASE_TIMEZONE);
if (!fileIdTemp.equals(fileId)) {
gotTransferring = false;
fileIdTemp = fileId;
}
if (statusTransferring.equals(transitionStateId)) {
gotTransferring = true;
transferStartedAt = createTime;
transitionStateFromId = transitionStateId;
continue;
}
if (statusTransferred.equals(transitionStateId) || statusError.equals(transitionStateId)) {
if (!gotTransferring) {
continue;
}
transferEndedAt = createTime;
durationInSeconds = Duration.between(transferStartedAt, transferEndedAt).toMillis() / 1000;
transitionStateToId = transitionStateId;
transferStatus = statusTransferred.equals(transitionStateId);
}
transferTimingIndexDTOs.add(new TransferTimingIndexDTO(
fileTransitionHistoryId,
visualCountValue,
somethingCountValue,
transitionStateFromId,
transitionStateToId,
transferStatus,
transferStartedAt,
transferEndedAt,
durationInSeconds
));
}
if (transferTimingIndexDTOs.size() > 0) {
// build insert statement
InsertValuesStep9<Record, Long, Integer, Integer, Integer, Integer, Byte, Timestamp, Timestamp, Long> insert = create.insertInto(TRANSFER_TIMING,
TRANSFER_TIMING_FILE_TRANSITION_HISTORY_ID,
TRANSFER_TIMING_VISUAL_COUNT,
TRANSFER_TIMING_SOMETHING_COUNT,
TRANSFER_TIMING_TRANSITION_STATE_FROM_ID,
TRANSFER_TIMING_TRANSITION_STATE_TO_ID,
TRANSFER_TIMING_TRANSFER_STATUS,
TRANSFER_TIMING_TRANSFER_STARTED_AT,
TRANSFER_TIMING_TRANSFER_ENDED_AT,
TRANSFER_TIMING_TRANSFER_TIME_SPENT_IN_SECONDS);
transferTimingIndexDTOs.forEach(e -> insert.values(
e.getFileTransitionHistoryId(),
e.getVisualCount(),
e.getsomethingCount(),
e.getTransitionStateFromId(),
e.getTransitionStateToId(),
ByteUtil.booleanToByte(e.getTransferStatus()),
Timestamp.valueOf(e.getTransferStartedAt().toLocalDateTime()),
Timestamp.valueOf(e.getTransferEndedAt().toLocalDateTime()),
e.getTransferTimeSpentInSeconds()));
// execute batch insert
insert.execute();
}
}
/**
* Create index table
*/
private void createIndexTable() {
create.createTableIfNotExists(TRANSFER_TIMING)
.column(TRANSFER_TIMING_FILE_TRANSITION_HISTORY_ID)
.column(TRANSFER_TIMING_VISUAL_COUNT)
.column(TRANSFER_TIMING_SOMETHING_COUNT)
.column(TRANSFER_TIMING_TRANSITION_STATE_FROM_ID)
.column(TRANSFER_TIMING_TRANSITION_STATE_TO_ID)
.column(TRANSFER_TIMING_TRANSFER_STATUS)
.column(TRANSFER_TIMING_TRANSFER_STARTED_AT)
.column(TRANSFER_TIMING_TRANSFER_ENDED_AT)
.column(TRANSFER_TIMING_TRANSFER_TIME_SPENT_IN_SECONDS)
.constraints(
constraint("primary").primaryKey(TRANSFER_TIMING_FILE_TRANSITION_HISTORY_ID)
)
.execute();
String sql = "ALTER TABLE " + TRANSFER_TIMING.getName() + " ENGINE = INNODB";
create.execute(sql);
create.createIndex("transition_state_from_id").on(TRANSFER_TIMING, TRANSFER_TIMING_TRANSITION_STATE_FROM_ID).execute();
create.createIndex("transition_state_to_id").on(TRANSFER_TIMING, TRANSFER_TIMING_TRANSITION_STATE_TO_ID).execute();
}
}
