From 18dbd71b5da8026d43636b5f3526dcecbc9283a1 Mon Sep 17 00:00:00 2001 From: "vasary.daniel" Date: Tue, 23 Nov 2021 14:03:41 +0000 Subject: [PATCH] git-tfs-id: [http://tfs.userrendszerhaz.hu:8080/tfs/DefaultCollection]$/MediaCube;C32355 --- .../run-mediacube-server.launch | 50 +--- .../user/jobengine/search/MediaFinder.java | 257 +++++++----------- .../user/jobengine/zk/model/SearchModel.java | 97 ++++--- 3 files changed, 156 insertions(+), 248 deletions(-) diff --git a/server/-configuration/run-mediacube-server.launch b/server/-configuration/run-mediacube-server.launch index 0a93a254..13188cc5 100644 --- a/server/-configuration/run-mediacube-server.launch +++ b/server/-configuration/run-mediacube-server.launch @@ -15,6 +15,8 @@ + + @@ -22,77 +24,33 @@ - - - - - - - - - - - - - - - + + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - diff --git a/server/user.jobengine.osgi.db/src/user/jobengine/search/MediaFinder.java b/server/user.jobengine.osgi.db/src/user/jobengine/search/MediaFinder.java index a25385ec..1cbb8650 100644 --- a/server/user.jobengine.osgi.db/src/user/jobengine/search/MediaFinder.java +++ b/server/user.jobengine.osgi.db/src/user/jobengine/search/MediaFinder.java @@ -3,6 +3,7 @@ package user.jobengine.search; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; +import java.sql.SQLException; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.List; @@ -33,29 +34,28 @@ public class MediaFinder implements IMediaFinder { StringBuilder sql = new StringBuilder(); sql.append("SELECT "); - - if(options.isCountOnly() == true) { - sql.append(" COUNT("); + + if (options.isCountOnly()) { + sql.append(" COUNT(1) AS NumberOfRecords"); + } else { + + sql.append("i.id itemid,"); + sql.append("i.title itemtitle,"); + sql.append("m.id mediaid,"); + sql.append("m.title mediatitle,"); + sql.append("m.archived,"); + sql.append("m.creation,"); + sql.append("m.created,"); + sql.append("m.modified,"); + sql.append("m.length,"); + sql.append("m.itemtypeid mediaitemtypeid,"); + sql.append(String.format("HIGHLIGHT(md.description, '', '', '%s') description,", + criteriasPattern)); + sql.append("it.name mediaitemtypename,"); + sql.append("m.houseid mediahouseid,"); + sql.append("mf.houseid mediafilehouseid,"); + sql.append("vw_mf.mediafilecount"); } - sql.append("i.id itemid,"); - sql.append("i.title itemtitle,"); - sql.append("m.id mediaid,"); - sql.append("m.title mediatitle,"); - sql.append("m.archived,"); - sql.append("m.creation,"); - sql.append("m.created,"); - sql.append("m.modified,"); - sql.append("m.length,"); - sql.append("m.itemtypeid mediaitemtypeid,"); - sql.append(String.format("HIGHLIGHT(md.description, '', '', '%s') description,", - criteriasPattern)); - sql.append("it.name mediaitemtypename,"); - sql.append("m.houseid mediahouseid,"); - sql.append("mf.houseid mediafilehouseid,"); - sql.append("vw_mf.mediafilecount"); - if(options.isCountOnly() == true) { - sql.append(") AS NumberOfRecords"); - } sql.append(" FROM ITEM i, MEDIA m, MEDIAFILE mf, MEDIADESCRIPTION md, VW_MEDIAFILES vw_mf, ITEMTYPE it"); sql.append(" WHERE i.id = md.itemid AND m.id = md.mediaid AND mf.id = md.mediafileid"); @@ -102,12 +102,17 @@ public class MediaFinder implements IMediaFinder { if (options.getDescLength() > 0) sql.append(" AND LENGTH(m.description) < " + options.getDescLength()); - sql.append(" ORDER BY "); - if (options.getOrderBy() == null) { - sql.append("itemtitle desc, mediaitemtypeid"); - } else { - sql.append(options.getOrderBy()); - sql.append(options.isOrderAscending() ? " ASC" : " DESC"); + if (!options.isCountOnly()) { + sql.append(" ORDER BY "); + if (options.getOrderBy() == null) { + sql.append("itemtitle desc, mediaitemtypeid"); + } else { + sql.append(options.getOrderBy()); + sql.append(options.isOrderAscending() ? " ASC" : " DESC"); + } + + sql.append(" LIMIT " + (options.getToResult() - options.getFromResult())); + sql.append(" OFFSET " + options.getFromResult()); } String query = sql.toString(); @@ -116,11 +121,11 @@ public class MediaFinder implements IMediaFinder { PreparedStatement st = connection.prepareStatement(query, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE); - // TODO Ettol elszall a 11.5-on a kereses - // if (criteria != null && criteria.trim().length() > 0) { - // st.setString(1, criteria); - // logger.info("Parameter is {}", criteria); - // } + // TODO Ettol elszall a 11.5-on a kereses + // if (criteria != null && criteria.trim().length() > 0) { + // st.setString(1, criteria); + // logger.info("Parameter is {}", criteria); + // } return st; } @@ -149,189 +154,83 @@ public class MediaFinder implements IMediaFinder { return sb.toString(); } - @Override - public SearchResult search(SearchOptions options) { - SearchResult result = new SearchResult<>(); + private long getNumberOfRecords(SearchOptions options) { + long result = 0; ResultSet rs = null; PreparedStatement st = null; + DefaultContext context = manager.getDbContext(); Connection connection = context.getConnection(); - SearchOptions optionsForCounting= options; - optionsForCounting.setCountOnly(true); - try { - int maxRecords = options.getToResult() - options.getFromResult(); - - //creating a query for checking resultset size - st= createStatement(connection, optionsForCounting.getText(), optionsForCounting); - st.close(); - rs= st.executeQuery(); - rs.close(); logger.info("Checking size"); - result.setItemCount(rs.getInt("NumberOfRecords")); - + options.setCountOnly(true); + // creating a query for checking resultset size st = createStatement(connection, options.getText(), options); - logger.info("Executing query, fetch size is {}, autocommit {}", st.getFetchSize(), - connection.getAutoCommit()); rs = st.executeQuery(); - logger.info("Result size is {}", result.getItemCount()); + if (rs.next()) + result = rs.getInt("NumberOfRecords"); - List resultList = null; - logger.info("Processing query result, maxRecords {}", maxRecords); - while (rs.next() && maxRecords > 0) { - ItemType it = new ItemType(); - it.setId(rs.getLong("mediaitemtypeid")); - it.setName(rs.getString("mediaitemtypename")); - - Item item = new Item(); - item.setId(rs.getLong("itemid")); - item.setTitle(rs.getString("itemtitle")); - - Media media = new Media(); - - media.setItemId(item.getId()); - media.setItemType(it); - media.setId(rs.getLong("mediaid")); - media.setTitle(rs.getString("mediatitle")); - media.setHouseId(rs.getString("mediahouseid")); - media.setCreation(rs.getTimestamp("creation")); - media.setCreated(rs.getTimestamp("created")); - media.setModified(rs.getTimestamp("modified")); - media.setArchived(rs.getTimestamp("archived")); - media.setLength(rs.getLong("length")); - media.setMediaFilesCount(rs.getInt("mediafilecount")); - media.setMediaFilesName(rs.getString("mediafilehouseid")); - - ArchivedMedia am = new ArchivedMedia(); - am.setMedia(media); - am.setItem(item); - - if (resultList == null) - resultList = new ArrayList<>(); - resultList.add(am); - - am.setRelevant(rs.getString("description")); - maxRecords--; - } - logger.info("Query result processed"); - result.setItems(resultList); connection.commit(); } catch (Exception e) { try { connection.rollback(); } catch (Exception e1) { - e1.printStackTrace(); + logger.throwing(e1); } manager.throwError(e); } finally { try { if (rs != null) rs.close(); - } catch (Exception e1) { + } catch (Exception e2) { } + try { if (st != null) st.close(); - } catch (Exception e1) { + } catch (Exception e2) { } manager.putDbContext(context); } - manager.traceOut(); - return result; } - public SearchResult search_v1(SearchOptions options) { - SearchResult result = new SearchResult<>(); - + private void getSearchResults(SearchResult searchResults, SearchOptions options) { ResultSet rs = null; PreparedStatement st = null; + DefaultContext context = manager.getDbContext(); Connection connection = context.getConnection(); - try { - int maxRecords = options.getToResult() - options.getFromResult(); + try { + options.setCountOnly(false); st = createStatement(connection, options.getText(), options); - - logger.info("Executing query, fetch size is {}, automcommit {}", st.getFetchSize(), - connection.getAutoCommit()); rs = st.executeQuery(); - logger.info("Checking size"); - rs.last(); - result.setItemCount(rs.getRow()); - logger.info("Result size is {}", result.getItemCount()); - - // set beginning position - if (options.getFromResult() > -1) { - if (options.getFromResult() <= result.getItemCount()) { - if (options.getFromResult() > 1) { - rs.absolute(options.getFromResult() - 1); - } else { - rs.beforeFirst(); - } - } else { - if (result.getItemCount() > 0) { - throw new Exception("Invalid searchResult 'from' position: " + options.getFromResult() - + " total records: " + result.getItemCount()); - } - } - } else { - rs.beforeFirst(); - } List resultList = null; - logger.info("Processing query result, maxRecords {}", maxRecords); - while (rs.next() && maxRecords > 0) { - ItemType it = new ItemType(); - it.setId(rs.getLong("mediaitemtypeid")); - it.setName(rs.getString("mediaitemtypename")); - - Item item = new Item(); - item.setId(rs.getLong("itemid")); - item.setTitle(rs.getString("itemtitle")); - - Media media = new Media(); - - media.setItemId(item.getId()); - media.setItemType(it); - media.setId(rs.getLong("mediaid")); - media.setTitle(rs.getString("mediatitle")); - media.setHouseId(rs.getString("mediahouseid")); - media.setCreation(rs.getTimestamp("creation")); - media.setCreated(rs.getTimestamp("created")); - media.setModified(rs.getTimestamp("modified")); - media.setArchived(rs.getTimestamp("archived")); - media.setLength(rs.getLong("length")); - media.setMediaFilesCount(rs.getInt("mediafilecount")); - media.setMediaFilesName(rs.getString("mediafilehouseid")); - - ArchivedMedia am = new ArchivedMedia(); - am.setMedia(media); - am.setItem(item); + while (rs.next()) { + ArchivedMedia am = createArchiveMedia(rs); if (resultList == null) resultList = new ArrayList<>(); resultList.add(am); am.setRelevant(rs.getString("description")); - // setRelevant(criterias, am, rs.getString("description")); - maxRecords--; } logger.info("Query result processed"); - result.setItems(resultList); + searchResults.setItems(resultList); connection.commit(); } catch (Exception e) { try { connection.rollback(); } catch (Exception e1) { - e1.printStackTrace(); + logger.throwing(e1); } manager.throwError(e); } finally { try { - if (rs != null) rs.close(); } catch (Exception e1) { @@ -344,8 +243,48 @@ public class MediaFinder implements IMediaFinder { manager.putDbContext(context); } - manager.traceOut(); + } + + private ArchivedMedia createArchiveMedia(ResultSet rs) throws SQLException { + ItemType it = new ItemType(); + it.setId(rs.getLong("mediaitemtypeid")); + it.setName(rs.getString("mediaitemtypename")); + + Item item = new Item(); + item.setId(rs.getLong("itemid")); + item.setTitle(rs.getString("itemtitle")); + + Media media = new Media(); + + media.setItemId(item.getId()); + media.setItemType(it); + media.setId(rs.getLong("mediaid")); + media.setTitle(rs.getString("mediatitle")); + media.setHouseId(rs.getString("mediahouseid")); + media.setCreation(rs.getTimestamp("creation")); + media.setCreated(rs.getTimestamp("created")); + media.setModified(rs.getTimestamp("modified")); + media.setArchived(rs.getTimestamp("archived")); + media.setLength(rs.getLong("length")); + media.setMediaFilesCount(rs.getInt("mediafilecount")); + media.setMediaFilesName(rs.getString("mediafilehouseid")); + + ArchivedMedia am = new ArchivedMedia(); + am.setMedia(media); + am.setItem(item); + return am; + } + + @Override + public SearchResult search(SearchOptions options) { + manager.traceIn(); + SearchResult result = new SearchResult<>(); + long numberOfRecords = getNumberOfRecords(options); + logger.info("Number of records {}", numberOfRecords); + result.setItemCount(numberOfRecords); + getSearchResults(result, options); + manager.traceOut(); return result; } diff --git a/server/user.mediacube.gui/src/user/jobengine/zk/model/SearchModel.java b/server/user.mediacube.gui/src/user/jobengine/zk/model/SearchModel.java index ca288203..f1bdec86 100644 --- a/server/user.mediacube.gui/src/user/jobengine/zk/model/SearchModel.java +++ b/server/user.mediacube.gui/src/user/jobengine/zk/model/SearchModel.java @@ -197,9 +197,9 @@ public class SearchModel extends AsyncBaseModel { } }; - // searchHistory = new ListModelList<>(SessionUtil.getUserSearchHistory()); - // setColumnOrder(); - // setUserSelectedFilters(); + // searchHistory = new ListModelList<>(SessionUtil.getUserSearchHistory()); + // setColumnOrder(); + // setUserSelectedFilters(); } private void addTypeFilters(List filters) { @@ -209,8 +209,9 @@ public class SearchModel extends AsyncBaseModel { @AfterCompose public void afterCompose(@ContextParam(ContextType.VIEW) Component view) { - //A MediaFileServlet a services/proxy alatt szolgal ki - //Clients.evalJavaScript("setSource('http://localhost:9080/services/proxy/20209535_1-20205617.mp4', 'video/mp4')"); + // A MediaFileServlet a services/proxy alatt szolgal ki + // Clients.evalJavaScript("setSource('http://localhost:9080/services/proxy/20209535_1-20205617.mp4', + // 'video/mp4')"); try { @@ -238,7 +239,7 @@ public class SearchModel extends AsyncBaseModel { showSearchTypeWarning(); tags = itemManager.getAllTags(); - //lekerdezesi hiba + // lekerdezesi hiba if (tags == null) Clients.showNotification("A cimkék inicializálása sikertelen!", "error", tagsToolbar, "start_center", 3000); @@ -289,11 +290,12 @@ public class SearchModel extends AsyncBaseModel { Executions.getCurrent().sendRedirect(email); } - // @GlobalCommand - // public void createFloatingJobMonitor() { - // Window window = (Window) Executions.createComponents("/pages/joblistwnd.zul", null, null); - // window.doOverlapped(); - // } + // @GlobalCommand + // public void createFloatingJobMonitor() { + // Window window = (Window) Executions.createComponents("/pages/joblistwnd.zul", + // null, null); + // window.doOverlapped(); + // } @Command public void createLowres() { @@ -405,7 +407,9 @@ public class SearchModel extends AsyncBaseModel { } void ensureVisible(int pos) { + registerTask(() -> { + logger.info("Scrolling to pos {} begin", pos); List children = itemsGrid.getRows().getChildren(); if (children.size() > 0) { Component top = children.get(pos); @@ -418,6 +422,7 @@ public class SearchModel extends AsyncBaseModel { positionToScroll = -1; } + logger.info("Scrolling to pos {} end", pos); preventAfterRender = false; }); timer.start(); @@ -467,16 +472,16 @@ public class SearchModel extends AsyncBaseModel { Store store = mf.getStore(); if (store == null) continue; - //TODO a tartalek nem tud lowres lenni, de a TSM-nek nincs HTTP bejegyzese - // if (store.isLowres()) { - // StoreUri sourceStoreUri = store.getSourceStoreUri(RemoteStoreProtocol.HTTP); - // URL base = new URL(sourceStoreUri.toString()); - // String relative = mf.getRelativePath().replace("\\", "/"); - // ret = new URL(base, relative).toString(); - // break; - // } - - //relative cim a sajat kiszolgalo miatt + // TODO a tartalek nem tud lowres lenni, de a TSM-nek nincs HTTP bejegyzese + // if (store.isLowres()) { + // StoreUri sourceStoreUri = store.getSourceStoreUri(RemoteStoreProtocol.HTTP); + // URL base = new URL(sourceStoreUri.toString()); + // String relative = mf.getRelativePath().replace("\\", "/"); + // ret = new URL(base, relative).toString(); + // break; + // } + + // relative cim a sajat kiszolgalo miatt StoreUri sourceStoreUri = store.getSourceStoreUri(RemoteStoreProtocol.HTTP); if (sourceStoreUri != null && sourceStoreUri.isStream()) { @@ -629,7 +634,8 @@ public class SearchModel extends AsyncBaseModel { @Command public void onAfterRenderGrid() { - //aszinkron fut le, kulon kezelendo a hiba + logger.info("onAfterRenderGrid begin"); + // aszinkron fut le, kulon kezelendo a hiba try { if (searchResult != null) @@ -640,13 +646,15 @@ public class SearchModel extends AsyncBaseModel { selectedObject = searchResult.getElementAt(0); highlight(selectedObject, true); notifyChange("selectedObject", "hasLowresMediaFile", "hasTSMMediaFile", "lowresMediaFilePath"); + logger.info("onAfterRenderGrid end"); return; } - if (preventAfterRender) + if (preventAfterRender) { + logger.info("onAfterRenderGrid end"); return; + } preventAfterRender = true; - logger.info("onAfterRenderGrid"); if (positionToScroll != -1) { ensureVisible(positionToScroll); @@ -655,6 +663,7 @@ public class SearchModel extends AsyncBaseModel { } catch (Exception e) { handleSearchError(e); } + logger.info("onAfterRenderGrid end"); } @Command @@ -670,10 +679,10 @@ public class SearchModel extends AsyncBaseModel { @Command public void onTagChanged(@ContextParam(ContextType.TRIGGER_EVENT) Event event) { - // source: filter | media, - // type : add | remove, - // tagtext: e.detail.data.value, - // index: e.detail.index + // source: filter | media, + // type : add | remove, + // tagtext: e.detail.data.value, + // index: e.detail.index JSONObject jsObject = (JSONObject) event.getData(); if (!jsObject.containsKey(TAGEVT_SOURCE)) @@ -730,10 +739,12 @@ public class SearchModel extends AsyncBaseModel { if (metaWindow != null) return; - // EventListener eventListener = e -> { - // EventQueues.lookup("my super queue", EventQueues.APPLICATION, true).unsubscribe(this); - // }; - // EventQueues.lookup("my super queue", EventQueues.APPLICATION, true).subscribe(eventListener); + // EventListener eventListener = e -> { + // EventQueues.lookup("my super queue", EventQueues.APPLICATION, + // true).unsubscribe(this); + // }; + // EventQueues.lookup("my super queue", EventQueues.APPLICATION, + // true).subscribe(eventListener); String template = "/pages/metadatadetails.zul"; EventListener listener = e -> { logger.info("Meta window closed"); @@ -798,7 +809,7 @@ public class SearchModel extends AsyncBaseModel { return; if (id == 0) { id = itemManager.addMediaTag(tagText, selectedObject.getMedia().getId()); - //frissitjuk az osszes Tagify control whitelist-jet + // frissitjuk az osszes Tagify control whitelist-jet tags.put(tagText, id); setSearchTags(); } else @@ -891,15 +902,15 @@ public class SearchModel extends AsyncBaseModel { int value = Integer.parseInt(source.getValue()); LocalDate now = LocalDate.now(); ZoneId zoneId = ZoneId.systemDefault(); - //az elozo, kovetkezo lepteteshez kell + // az elozo, kovetkezo lepteteshez kell selectedDateOption = value; switch (value) { - //nap + // nap case 1: searchFrom = Date.from(now.atStartOfDay(zoneId).toInstant()); searchTo = Date.from(now.plusDays(1).atStartOfDay(zoneId).toInstant()); break; - //het + // het case 2: ZonedDateTime zdt = now.atStartOfDay(zoneId); // ISO 8601, Monday is first day of week @@ -908,20 +919,20 @@ public class SearchModel extends AsyncBaseModel { ZonedDateTime firstOfNextWeek = firstOfWeek.plusWeeks(1); searchTo = Date.from(firstOfNextWeek.toInstant()); break; - //honap + // honap case 3: searchFrom = Date.from(now.with(firstDayOfMonth()).atStartOfDay(zoneId).toInstant()); searchTo = Date.from(now.with(firstDayOfNextMonth()).atStartOfDay(zoneId).toInstant()); break; - //ev + // ev case 4: searchFrom = Date.from(now.with(firstDayOfYear()).atStartOfDay(zoneId).toInstant()); searchTo = Date.from(now.with(firstDayOfNextYear()).atStartOfDay(zoneId).toInstant()); break; - //elozo + // elozo case 100: break; - //kovetkezo + // kovetkezo case 200: break; case 300: @@ -959,7 +970,7 @@ public class SearchModel extends AsyncBaseModel { if (mediaId > 0) mediaTags = itemManager.getMediaTags(mediaId); - //inicializaljuk, hogy az addTag event ezeket mar ne akarja meg 1x hozzadni + // inicializaljuk, hogy az addTag event ezeket mar ne akarja meg 1x hozzadni StringBuilder sb = new StringBuilder(); if (mediaTags != null) { for (String tag : mediaTags) { @@ -1045,11 +1056,11 @@ public class SearchModel extends AsyncBaseModel { selectedObject.setMedia(itemManager.getMedia(mediaId)); selectedObject.setItem(itemManager.getItem(selectedObject.getMedia().getItemId())); - //tags + // tags setMediaTags(mediaId); try { - //proxy + // proxy String video = getLowresMediaFilePath(); if (video == null) { Clients.evalJavaScript("setSource(null, null)"); -- 2.54.0