From 11fa5a0d420ec81c769684770fcb4aeaa0c63b21 Mon Sep 17 00:00:00 2001 From: elgekko Date: Thu, 9 Mar 2023 10:29:24 +0100 Subject: [PATCH] DB modositasok, kereses merese --- .../scripts/041_alter_media_description.sql | 14 ++ .../042_create_function_TSMStoreId.sql | 13 ++ .../scripts/043_add_filecount_to_media.sql | 38 +++++ .../IExecutionDurationNotification.java | 8 ++ .../src/user/jobengine/search/SearchSQL.java | 130 ++++++++++++++++++ 5 files changed, 203 insertions(+) create mode 100644 server/user.jobengine.osgi.db/migrations/scripts/041_alter_media_description.sql create mode 100644 server/user.jobengine.osgi.db/migrations/scripts/042_create_function_TSMStoreId.sql create mode 100644 server/user.jobengine.osgi.db/migrations/scripts/043_add_filecount_to_media.sql create mode 100644 server/user.jobengine.osgi.db/src/user/jobengine/search/IExecutionDurationNotification.java create mode 100644 server/user.jobengine.osgi.db/src/user/jobengine/search/SearchSQL.java diff --git a/server/user.jobengine.osgi.db/migrations/scripts/041_alter_media_description.sql b/server/user.jobengine.osgi.db/migrations/scripts/041_alter_media_description.sql new file mode 100644 index 00000000..f14a4f66 --- /dev/null +++ b/server/user.jobengine.osgi.db/migrations/scripts/041_alter_media_description.sql @@ -0,0 +1,14 @@ +-- // Extends MEDIA DESCRIPTION from VARCHAR to CLOB +-- Migration SQL that makes the change goes here. + +ALTER TABLE MEDIA + ALTER COLUMN DESCRIPTION + SET DATA TYPE CLOB +@ + +-- //@UNDO +-- SQL to undo the change goes here. +ALTER TABLE MEDIA + ALTER COLUMN DESCRIPTION + SET DATA TYPE VARCHAR(32000) +@ diff --git a/server/user.jobengine.osgi.db/migrations/scripts/042_create_function_TSMStoreId.sql b/server/user.jobengine.osgi.db/migrations/scripts/042_create_function_TSMStoreId.sql new file mode 100644 index 00000000..9d7f40fd --- /dev/null +++ b/server/user.jobengine.osgi.db/migrations/scripts/042_create_function_TSMStoreId.sql @@ -0,0 +1,13 @@ +-- // Extends MEDIA DESCRIPTION from VARCHAR to CLOB +-- Migration SQL that makes the change goes here. + +CREATE FUNCTION TSMStoreId() +RETURNS BIGINT +RETURN + SELECT id FROM STORE WHERE NAME='TSM' +@ + +-- //@UNDO +-- SQL to undo the change goes here. +DROP FUNCTION TSMStoreId +@ diff --git a/server/user.jobengine.osgi.db/migrations/scripts/043_add_filecount_to_media.sql b/server/user.jobengine.osgi.db/migrations/scripts/043_add_filecount_to_media.sql new file mode 100644 index 00000000..a4e2ec63 --- /dev/null +++ b/server/user.jobengine.osgi.db/migrations/scripts/043_add_filecount_to_media.sql @@ -0,0 +1,38 @@ +-- // Create and fill FILECOUNT in MEDIA +DROP TRIGGER TRG_MEDIA_AFTER_UPDATE +@ + +ALTER TABLE MEDIA ADD COLUMN FILECOUNT INT +@ + +UPDATE MEDIA m +SET m.FILECOUNT = ( + SELECT f.MEDIAFILECOUNT FROM FILECOUNTS f WHERE f.MEDIAID = m.ID +) WHERE EXISTS ( + SELECT 1 FROM FILECOUNTS f WHERE f.MEDIAID = m.ID +) +@ + +CREATE INDEX IDX_MEDIA_FILECOUNT ON MEDIA (FILECOUNT) +@ + +CREATE OR REPLACE TRIGGER TRG_MEDIA_AFTER_UPDATE +AFTER UPDATE ON MEDIA +REFERENCING NEW AS n +FOR EACH ROW +BEGIN ATOMIC + UPDATE MEDIADESCRIPTION SET DESCRIPTION = FULLDESC(ITEMID, MEDIAID, MEDIAFILEID) WHERE MEDIAID = N.ID; +END +@ + + +--CALL SYSPROC.ADMIN_CMD('REORG TABLE DB2ADMIN.MEDIA') +--@ + +-- //@UNDO + +ALTER TABLE MEDIA DROP COLUMN FILECOUNT +@ + +CALL SYSPROC.ADMIN_CMD('REORG TABLE MEDIA') +@ diff --git a/server/user.jobengine.osgi.db/src/user/jobengine/search/IExecutionDurationNotification.java b/server/user.jobengine.osgi.db/src/user/jobengine/search/IExecutionDurationNotification.java new file mode 100644 index 00000000..741bb16e --- /dev/null +++ b/server/user.jobengine.osgi.db/src/user/jobengine/search/IExecutionDurationNotification.java @@ -0,0 +1,8 @@ +package user.jobengine.search; + +import java.time.Duration; +import java.time.Instant; + +public interface IExecutionDurationNotification { + void notify(Duration duration); +} diff --git a/server/user.jobengine.osgi.db/src/user/jobengine/search/SearchSQL.java b/server/user.jobengine.osgi.db/src/user/jobengine/search/SearchSQL.java new file mode 100644 index 00000000..33bfe44f --- /dev/null +++ b/server/user.jobengine.osgi.db/src/user/jobengine/search/SearchSQL.java @@ -0,0 +1,130 @@ +package user.jobengine.search; + +import java.util.List; +import java.util.stream.Collectors; + +import org.apache.ibatis.jdbc.SQL; + +public class SearchSQL { + + private static String getCriteriasRegexPattern(String criteria) { + String[] criterias = criteria.replace("*", "").replace("+", " ").split(" "); + if (criteria.startsWith("\"") && criteria.endsWith("\"")) + criterias = new String[] { criteria.replace("\"", "") }; + + StringBuilder sb = new StringBuilder(); + for (String word : criterias) { + if (sb.length() > 0) + sb.append("|"); + sb.append(word); + } + return sb.toString(); + } + + public static SQL descriptionsQuery(String criteria, List mediaIds) { + String criteriasPattern = getCriteriasRegexPattern(criteria); + List ids = mediaIds.stream().map(id -> String.valueOf(id)).collect(Collectors.toList()); + SQL sql = new SQL(); + sql.SELECT("mediaid"); + sql.SELECT(String.format("HIGHLIGHT(description, '', '', '%s') description", criteriasPattern)); + sql.FROM("MEDIADESCRIPTION"); + sql.WHERE(String.format("mediaid IN (%s)", String.join(",", ids))); + return sql; + } + +// private PreparedStatement createGroupingStatement(Connection connection, String criteria, SearchOptions options) throws Exception { +// boolean simpleSearch = manager.getSystemConfig().value("datasource.mediacube.simple-search", true); +// String criteriasPattern = getCriteriasRegexPattern(criteria); +// +// SQL innerSql = new SQL(); +// innerSql.SELECT("max(vi.mediaid)"); +// innerSql.FROM("VW_ITEMS_WITH_PATH vi", "MEDIADESCRIPTION md"); +// innerSql.WHERE("vi.mediafileid = md.mediafileid"); +// +// if (simpleSearch) { +// // TODO ezen az agon nem jo!!!!! +// String simpleTextCriteria = formatSimpleSearchValue(criteria); +// if (simpleTextCriteria != null && simpleTextCriteria.trim().length() > 0) { +// innerSql.AND(); +// innerSql.WHERE("("); +// innerSql.WHERE(String.format("%s LIKE '%%%s%%'", "vi.mediatitle", simpleTextCriteria)); +// innerSql.OR(); +// innerSql.WHERE(String.format("%s LIKE '%%%s%%'", "vi.itemtitle", simpleTextCriteria)); +// innerSql.OR(); +// innerSql.WHERE(String.format("%s LIKE '%%%s%%'", "vi.mediarelativepath", simpleTextCriteria)); +// innerSql.WHERE(")"); +// } +// } else { +// String textCriteria = formatSearchValue(criteria); +// if (textCriteria != null && textCriteria.trim().length() > 0) { +// innerSql.AND(); +// innerSql.WHERE(String.format("CONTAINS(md.description, '%s') >= 1", textCriteria)); +// } +// } +// innerSql.GROUP_BY("vi.mediarelativepath"); +// +// SQL mainSql = new SQL(); +// if (options.isCountOnly()) +// mainSql.SELECT("COUNT(1) AS NumberOfRecords"); +// else +// mainSql.SELECT("vi.itemid", "vi.itemtitle", "vi.mediaid", "vi.mediatitle", "vi.mediarelativepath", "vi.mediaarchived", "vi.mediacreation", +// "vi.medialength", "vf.mediafilecount", +// String.format("HIGHLIGHT(md.description, '', '', '%s') description", criteriasPattern)); +// +// mainSql.FROM("VW_ITEMS_WITH_PATH vi", "VW_MEDIAFILES vf", "MEDIADESCRIPTION md"); +// mainSql.WHERE("vf.mediaid = vi.mediaid"); +// mainSql.AND(); +// mainSql.WHERE("vi.mediafileid = md.mediafileid"); +// mainSql.AND(); +// mainSql.WHERE(String.format("vi.mediaid IN (\r\n%s\r\n)", innerSql.toString())); +// +// StringBuilder criteriaForType = options.getCriteriaForType(); +// if (criteriaForType != null) { +// mainSql.AND(); +// mainSql.WHERE(String.format("vi.mediaitemtypeid IN (%s)", criteriaForType.toString())); +// } +// +// StringBuilder criteriaForTag = options.getCriteriaForTag(); +// if (criteriaForTag != null) { +// SQL tagSql = new SQL(); +// tagSql.SELECT("mediaid"); +// tagSql.FROM("MEDIATAGS"); +// tagSql.WHERE(String.format("vi.mediaitemtypeid IN (%s)", criteriaForTag.toString())); +// +// mainSql.AND(); +// mainSql.WHERE(String.format("vi.mediaid IN (%s)", tagSql.toString())); +// } +// +// if (options.getFrom() != null) { +// mainSql.AND(); +// mainSql.WHERE(String.format("vi.mediacreation > '%s'", df.format(options.getFrom()))); +// } +// +// if (options.getTo() != null) { +// mainSql.AND(); +// mainSql.WHERE(String.format("vi.mediacreation < '%s'", df.format(options.getTo()))); +// } +// +// if (options.isMissingProxy()) { +// mainSql.AND(); +// mainSql.WHERE("vf.mediafilecount = 1"); +// } +// +// if (options.getDescLength() > 0) { +// mainSql.AND(); +// mainSql.WHERE(String.format("LENGTH(vi.mediadescription) < %s", options.getDescLength())); +// } +// +// if (!options.isCountOnly() && (options.getToResult() != 0)) { +// mainSql.ORDER_BY("vi.mediacreation DESC"); +// mainSql.LIMIT((options.getToResult() - options.getFromResult())); +// mainSql.OFFSET(options.getFromResult()); +// } +// +// String query = mainSql.toString(); +// logger.info(query); +// PreparedStatement st = connection.prepareStatement(query, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE); +// return st; +//} + +} -- 2.54.0