source: trunk/www/views/reporterlists/index.jsp @ 5711

Last change on this file since 5711 was 5711, checked in by Nicklas Nordborg, 10 years ago

References #1618: Improve "Reporter search" performance in experiment explorer

Different implementation on "Reporter search" that uses the cached reporter ids and use a regular query against the Reporters table only. As a result, the "Create reporter list" function needed to fixed as it can no longer use the query. Instead it also uses the cached reporter ids.

Added filter condition for bioassays when loading the "Reporter view" page since it seems to be a lot quicker (see comment 4).

Fixed an issue with generating incorrect SQL when filtering on a reporter list. I think this broke as part of #903, but has gone unnoticed since then. A join to the reporter list table was missing due to no auto-join support in DynamicPositionQuery and DynamicReporterQuery. Auto-join support has now been added to those query implementations.

Everything is a lot quicker now on my development machine. I have 2.5M reporters in the reporter table and an experiment with 24M rows of spot data in 500 bioassays. So far no action seems to take more than a couple of seconds to complete.

  • Property svn:eol-style set to native
  • Property svn:keywords set to Date Id
File size: 18.0 KB
Line 
1<%-- $Id: index.jsp 5711 2011-08-30 13:49:58Z nicklas $
2  ------------------------------------------------------------------
3  Copyright (C) 2006 Jari Häkkinen, Nicklas Nordborg, Martin Svensson
4
5  This file is part of BASE - BioArray Software Environment.
6  Available at http://base.thep.lu.se/
7
8  BASE is free software; you can redistribute it and/or
9  modify it under the terms of the GNU General Public License
10  as published by the Free Software Foundation; either version 3
11  of the License, or (at your option) any later version.
12
13  BASE is distributed in the hope that it will be useful,
14  but WITHOUT ANY WARRANTY; without even the implied warranty of
15  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
16  GNU General Public License for more details.
17
18  You should have received a copy of the GNU General Public License
19  along with BASE. If not, see <http://www.gnu.org/licenses/>.
20  ------------------------------------------------------------------
21
22  @author Nicklas
23  @version 2.0
24--%>
25<%@ page pageEncoding="UTF-8" session="false"
26  import="net.sf.basedb.core.SessionControl"
27  import="net.sf.basedb.core.DbControl"
28  import="net.sf.basedb.core.Item"
29  import="net.sf.basedb.core.Include"
30  import="net.sf.basedb.core.ReporterList"
31  import="net.sf.basedb.core.Reporter"
32  import="net.sf.basedb.core.Permission"
33  import="net.sf.basedb.core.Type"
34  import="net.sf.basedb.core.ItemContext"
35  import="net.sf.basedb.core.DataQuery"
36  import="net.sf.basedb.core.ItemQuery"
37  import="net.sf.basedb.core.DynamicQuery"
38  import="net.sf.basedb.core.DataResultIterator"
39  import="net.sf.basedb.core.DynamicResultIterator"
40  import="net.sf.basedb.core.MultiPermissions"
41  import="net.sf.basedb.core.OwnedItem"
42  import="net.sf.basedb.core.PermissionDeniedException"
43  import="net.sf.basedb.core.ItemAlreadyExistsException"
44  import="net.sf.basedb.core.data.ReporterData"
45  import="net.sf.basedb.core.query.Query"
46  import="net.sf.basedb.core.query.Restrictions"
47  import="net.sf.basedb.core.query.Expressions"
48  import="net.sf.basedb.core.query.Hql"
49  import="net.sf.basedb.core.query.SqlResult"
50  import="net.sf.basedb.util.RemovableUtil"
51  import="net.sf.basedb.util.ShareableUtil"
52  import="net.sf.basedb.util.OwnableUtil"
53  import="net.sf.basedb.clients.web.Base"
54  import="net.sf.basedb.clients.web.WebException"
55  import="net.sf.basedb.util.Values"
56  import="net.sf.basedb.clients.web.ExperimentExplorer"
57  import="net.sf.basedb.clients.web.util.HTML"
58  import="net.sf.basedb.core.plugin.GuiContext"
59  import="net.sf.basedb.util.extensions.ExtensionsInvoker"
60  import="net.sf.basedb.clients.web.extensions.ExtensionsControl"
61  import="net.sf.basedb.clients.web.extensions.JspContext"
62  import="net.sf.basedb.clients.web.extensions.edit.EditUtil"
63  import="net.sf.basedb.clients.web.extensions.edit.OnSaveRenderer"
64  import="java.util.Enumeration"
65  import="java.util.Set"
66  import="java.util.HashSet"
67  import="java.util.List"
68  import="java.util.ArrayList"
69  import="java.util.Collections"
70  import="java.util.Arrays"
71%>
72<%@ taglib prefix="base" uri="/WEB-INF/base.tld" %>
73<%!
74  private static final ItemContext defaultContext = Base.createDefaultContext("name", "name,externalId,description");
75  private static final Item itemType = Item.REPORTERLIST;
76%>
77<%
78final SessionControl sc = Base.getExistingSessionControl(pageContext, true);
79final String ID = sc.getId();
80final String cmd = request.getParameter("cmd");
81final String root = request.getContextPath()+"/";
82final String mode = request.getParameter("mode");
83final String callback = request.getParameter("callback");
84final String itemId = request.getParameter("item_id");
85final String listPage = "list_reporterlists.jsp?ID="+ID
86  +(mode == null ? "" : "&mode="+mode)
87  +(callback == null ? "" : "&callback="+callback)
88  +(itemId == null ? "" : "&item_id="+itemId);
89final String viewPage = "view_reporterlist.jsp?ID="+ID;
90final String editPage = "edit_reporterlist.jsp?ID="+ID;
91
92String forward = null;
93String redirect = null;
94String message = null;
95DbControl dc = null;
96
97try
98{
99  if (cmd == null || "List".equals(cmd))
100  {
101    // Display the list page without updatinging the current context
102    Base.getAndSetCurrentContext(sc, itemType, null, defaultContext, true);
103    redirect = listPage;
104  }
105  else if ("UpdateContext".equals(cmd))
106  {
107    // Display the list page after updating the current context from the request parameters
108    Base.getAndSetCurrentContext(sc, itemType, pageContext, defaultContext);
109    redirect = listPage;
110  }
111  else if ("LoadContext".equals(cmd))
112  {
113    // Display the list page after loading a saved context
114    int contextId = Values.getInt(request.getParameter("context"));
115    Base.loadContext(sc, contextId, defaultContext);
116    redirect = listPage;
117  }
118
119  else if ("ViewItem".equals(cmd))
120  {
121    // Display the view page for a single item
122    ItemContext cc = Base.getAndSetCurrentContext(sc, itemType, pageContext, defaultContext);
123    forward = viewPage;
124  }
125  else if ("EditItem".equals(cmd))
126  {
127    // Display the edit page for a single item (should be opened in a popup)
128    ItemContext cc = Base.getAndSetCurrentContext(sc, itemType, pageContext, defaultContext);
129    redirect = editPage;
130  }
131  else if ("NewItem".equals(cmd))
132  {
133    // Display the edit page for a new item (should be opened in a popup)
134    if (!sc.hasPermission(Permission.CREATE, itemType))
135    {
136      throw new PermissionDeniedException(Permission.CREATE, itemType.toString());
137    }
138    ItemContext cc = Base.getAndSetCurrentContext(sc, itemType, pageContext, defaultContext);
139    cc.setId(0);
140    forward = editPage;
141  }
142  else if ("UpdateItem".equals(cmd))
143  {
144    // Update the properties on an item (will close the popup)
145    ItemContext cc = Base.getAndSetCurrentContext(sc, itemType, null, defaultContext);
146    dc = sc.newDbControl();
147    ReporterList rl = (ReporterList)cc.getObject("item");
148    if (rl == null)
149    {
150      rl = ReporterList.getNew(dc);
151      dc.saveItem(rl);
152     
153      // Add individual reporters to the list
154      String which = request.getParameter("which");
155      if (which != null)
156      {
157        Item fromContext = Item.valueOf(request.getParameter("fromContext"));
158        String subContext = Values.getString(request.getParameter("subContext"), "");
159        ItemContext reporterContext = sc.getCurrentContext(fromContext, subContext);
160        Query query = reporterContext.getQuery();
161        Object listCreator = reporterContext.getObject("reporter-list-creator");
162        if (query != null)
163        {
164          System.out.println("list-query: " + query.toQl(dc));
165          if ("all".equals(which))
166          {
167            query.setFirstResult(0);
168            query.setMaxResults(-1);
169          }
170          else if ("selected".equals(which))
171          {
172            query.setFirstResult(0);
173            query.setMaxResults(-1);
174            Integer[] itemIds = Values.getInt(request.getParameter("items").split(","));
175            query.restrict(
176              Restrictions.in(
177                Hql.property("id"),
178                Expressions.parameter("_selected_")
179              )
180            );
181            query.setParameter("_selected_", Arrays.asList(itemIds), Type.INT);     
182          }
183          // else -- no modifications to the query mean that we only get the current page
184         
185          if (query instanceof DataQuery)
186          {
187            DataResultIterator<ReporterData> result = ((DataQuery<ReporterData>)query).iterate(dc);
188            while (result.hasNext())
189            {
190              ReporterData reporter = result.next();
191              if (reporter != null) rl.addReporter(reporter, null);
192            }
193          }
194          else if (query instanceof DynamicQuery)
195          {
196            DynamicResultIterator result = ((DynamicQuery)query).iterate(dc);
197            while (result.hasNext())
198            {
199              SqlResult i = result.next();
200              int reporterId = i.getInt(1);
201              if (reporterId != 0)
202              {
203                rl.addReporter(Reporter.getProxy(reporterId), null);
204              }
205            }
206          }
207        }
208        else if (listCreator instanceof ExperimentExplorer)
209        {
210          // Special case when creating reporter list from experiment explorer
211          // since the desired reporter id:s are already cached
212          ExperimentExplorer explorer = (ExperimentExplorer)listCreator;
213          int start = 0;
214          int end = 0;
215          int totalReporters = explorer.getMatchingReporters(dc);
216          if ("all".equals(which))
217          {
218            end = totalReporters;
219          }
220          else
221          {
222            start = reporterContext.getPage() * reporterContext.getRowsPerPage();
223            end = start + reporterContext.getRowsPerPage();
224          }
225         
226          for (int index = start; index < end && index < totalReporters; ++index)
227          {
228            rl.addReporter(Reporter.getProxy(explorer.getReporterId(index)), null);
229          }
230        }
231      }
232     
233      // Add reporters from other lists
234      String create = Values.getStringOrNull(request.getParameter("create"));
235      if (create != null)
236      {
237        List<ReporterList> lists = new ArrayList<ReporterList>(cc.getSelected().size());
238        for (int listId : cc.getSelected())
239        {
240          lists.add(ReporterList.getById(dc, listId));
241        }
242        if ("union".equals(create))
243        {
244          rl.addUnion(lists);
245        }
246        else if ("intersection".equals(create))
247        {
248          rl.addIntersection(lists);
249        }
250        else if ("count".equals(create))
251        {
252          int minCount = Values.getInt(request.getParameter("minCount"), 1);
253          rl.addIfPresentIn(minCount, lists.size(), lists);
254        }
255      }
256      message = "Reporter list created with " + rl.getSize() + " reporter(s)";
257    }
258    else
259    {
260      dc.reattachItem(rl, false);
261      message = "Reporter list updated";
262    }
263    JspContext jspContext = ExtensionsControl.createContext(dc, pageContext, GuiContext.item(Item.REPORTERLIST), rl);
264    ExtensionsInvoker invoker = EditUtil.useOnSaveExtensions(jspContext);
265    try
266    {
267      rl.setName(Values.getStringOrNull(request.getParameter("name")));
268      rl.setExternalId(Values.getStringOrNull(request.getParameter("external_id")));
269      rl.setDescription(Values.getStringOrNull(request.getParameter("description")));
270     
271      // OnSave extensions
272      invoker.render(OnSaveRenderer.ON_SAVE);
273      dc.commit();
274      invoker.render(OnSaveRenderer.ON_COMMIT);
275    }
276    catch (Exception ex)
277    {
278      invoker.render(OnSaveRenderer.onRollback(ex));
279      throw ex;
280    }
281    finally
282    {
283      cc.removeObject("item");
284    }
285  }
286  else if ("DeleteItem".equals(cmd))
287  {
288    // Delete a single item and then return to the view page
289    dc = sc.newDbControl();
290    ItemContext cc = Base.getAndSetCurrentContext(sc, itemType, pageContext, defaultContext);
291    RemovableUtil.setRemoved(dc, itemType, Collections.singleton(cc.getId()), true);
292    dc.commit();
293    redirect = viewPage;
294  }
295  else if ("DeleteItems".equals(cmd))
296  {
297    // Delete all selected items on the list page
298    dc = sc.newDbControl();
299    ItemContext cc = Base.getAndSetCurrentContext(sc, itemType, pageContext, defaultContext);
300    int numTotal = cc.getSelected().size();
301    int numRemoved = RemovableUtil.setRemoved(dc, itemType, cc.getSelected(), true);
302    dc.commit();
303    if (numTotal != numRemoved)
304    {
305      message = (numRemoved == 0 ? "No" : "Only "+numRemoved+" of "+numTotal) + " items could be deleted, because you have no DELETE permission";
306    }
307    redirect = listPage+(message != null ? "&popmessage="+HTML.urlEncode(message) : "");
308  }
309  else if ("RestoreItem".equals(cmd))
310  {
311    // Restore a single item and then return to the view page
312    dc = sc.newDbControl();
313    ItemContext cc = Base.getAndSetCurrentContext(sc, itemType, pageContext, defaultContext);
314    RemovableUtil.setRemoved(dc, itemType, Collections.singleton(cc.getId()), false);
315    dc.commit();
316    redirect = viewPage;
317  }
318  else if ("RestoreItems".equals(cmd))
319  {
320    // Restore all selected items on the list page
321    dc = sc.newDbControl();
322    ItemContext cc = Base.getAndSetCurrentContext(sc, itemType, pageContext, defaultContext);
323    int numTotal = cc.getSelected().size();
324    int numRemoved = RemovableUtil.setRemoved(dc, itemType, cc.getSelected(), false);
325    dc.commit();
326    if (numTotal != numRemoved)
327    {
328      message = (numRemoved == 0 ? "No" : "Only "+numRemoved+" of "+numTotal) + " items could be restored, because you have no WRITE permission";
329    }
330    redirect = listPage+(message != null ? "&popmessage="+HTML.urlEncode(message) : "");
331  }
332  else if ("ShareItem".equals(cmd))
333  {
334    // Display a popup window for sharing a single item
335    dc = sc.newDbControl();
336    ItemContext cc = Base.getAndSetCurrentContext(sc, itemType, pageContext, defaultContext);
337    MultiPermissions permissions = ShareableUtil.getMultiPermissions(dc, itemType, Collections.singleton(cc.getId()));
338    dc.close();
339    cc.setObject("MultiPermissions", permissions);
340    redirect = "../../common/share/share.jsp?ID="+ID+"&item_type="+itemType.name();
341  }
342  else if ("ShareItems".equals(cmd))
343  {
344    // Display a popup window for sharing all selected items on the list page
345    dc = sc.newDbControl();
346    ItemContext cc = Base.getAndSetCurrentContext(sc, itemType, pageContext, defaultContext);
347    MultiPermissions permissions = ShareableUtil.getMultiPermissions(dc, itemType, cc.getSelected());
348    dc.close();
349    cc.setObject("MultiPermissions", permissions);
350    redirect = "../../common/share/share.jsp?ID="+ID+"&item_type="+itemType.name();
351  }
352  else if ("SetOwnerOfItem".equals(cmd))
353  {
354    // Change owner of items selected on a list page
355    dc = sc.newDbControl();
356    ItemContext cc = Base.getAndSetCurrentContext(sc, itemType, pageContext, defaultContext);
357    OwnedItem item = (OwnedItem)itemType.getById(dc, cc.getId());
358    cc.setObject("OwnedItems", Collections.singleton(item));
359    redirect = "../../common/ownership/ownership.jsp?ID="+ID+"&item_type="+itemType.name();
360  }
361  else if ("SetOwnerOfItems".equals(cmd))
362  {
363    // Change owner of items selected on a list page
364    dc = sc.newDbControl();
365    ItemContext cc = Base.getAndSetCurrentContext(sc, itemType, pageContext, defaultContext);
366    Set<OwnedItem> items = new HashSet<OwnedItem>();
367    for (Integer id : cc.getSelected())
368    {
369      if (id != null) items.add((OwnedItem)itemType.getById(dc, id));
370    }
371    dc.close();
372    cc.setObject("OwnedItems", items);
373    redirect = "../../common/ownership/ownership.jsp?ID="+ID+"&item_type="+itemType.name();
374  }
375  else if ("ExportItems".equals(cmd))
376  {
377    // Run an export plugin in a list context
378    ItemContext cc = Base.getAndSetCurrentContext(sc, itemType, pageContext, defaultContext);
379    final ItemQuery<ReporterList> query = ReporterList.getQuery();
380    dc = sc.newDbControl();
381    cc.configureQuery(dc, query, true);
382    dc.close();
383    cc.setQuery(query);
384    redirect = "../../common/export/index.jsp?ID="+ID+"&cmd=SelectPlugin&item_type="+itemType.name()+"&context_type=LIST&title=Export+reporter+lists";
385  }
386  else if ("ExportItem".equals(cmd))
387  {
388    // Run an export plugin in single-item context
389    ItemContext cc = Base.getAndSetCurrentContext(sc, itemType, pageContext, defaultContext);
390    redirect = "../../common/export/index.jsp?ID="+ID+"&cmd=SelectPlugin&item_type="+itemType.name()+"&context_type=ITEM&title=Export+reporter+list";
391  }
392  else if ("ImportItems".equals(cmd))
393  {
394    // Run an import plugin in a list context
395    ItemContext cc = Base.getAndSetCurrentContext(sc, itemType, pageContext, defaultContext);
396    final ItemQuery<ReporterList> query = ReporterList.getQuery();
397    dc = sc.newDbControl();
398    cc.configureQuery(dc, query, true);
399    dc.close();
400    cc.setQuery(query);
401    redirect = "../../common/import/index.jsp?ID="+ID+"&cmd=SelectPlugin&item_type="+itemType.name()+"&context_type=LIST&title=Import+reporter+lists";
402  }
403  else if ("ImportItem".equals(cmd))
404  {
405    // Run an import plugin in single-item context
406    ItemContext cc = Base.getAndSetCurrentContext(sc, itemType, pageContext, defaultContext);
407    redirect = "../../common/import/index.jsp?ID="+ID+"&cmd=SelectPlugin&item_type="+itemType.name()+"&context_type=ITEM&title=Import+reporter+list";
408  }
409  else if ("RunListPlugin".equals(cmd))
410  {
411    // Run another plugin in a list context
412    ItemContext cc = Base.getAndSetCurrentContext(sc, itemType, pageContext, defaultContext);
413    final ItemQuery<ReporterList> query = ReporterList.getQuery();
414    dc = sc.newDbControl();
415    cc.configureQuery(dc, query, true);
416    dc.close();
417    cc.setQuery(query);
418    redirect = "../../common/plugin/index.jsp?ID="+ID+"&cmd=SelectPlugin&item_type="+itemType.name()+"&context_type=LIST&main_type=OTHER&title=Run+plugin";
419  }
420  else if ("RunPlugin".equals(cmd))
421  {
422    // Run another plugin in single-item context
423    ItemContext cc = Base.getAndSetCurrentContext(sc, itemType, pageContext, defaultContext);
424    redirect = "../../common/plugin/index.jsp?ID="+ID+"&cmd=SelectPlugin&item_type="+itemType.name()+"&context_type=ITEM&main_type=OTHER&title=Run+plugin";
425  }
426  else if ("MergeItem".equals(cmd))
427  {
428    ItemContext cc = Base.getAndSetCurrentContext(sc, itemType, null, defaultContext);
429    dc = sc.newDbControl();
430    ReporterList rl = (ReporterList)cc.getObject("item");
431    dc.reattachItem(rl, false);
432   
433    String mergeType = request.getParameter("mergeType");
434    String sourceMerge = request.getParameter("sourceMerge");
435   
436    String[] sourceListIds = Values.getString(request.getParameter("sourceLists")).split(",");
437    List<ReporterList> sourceLists = new ArrayList<ReporterList>(sourceListIds.length);
438    for (int i = 0; i < sourceListIds.length; ++i)
439    {
440      int listId = Values.getInt(sourceListIds[i], -1);
441      if (listId != -1) sourceLists.add(ReporterList.getById(dc, listId));
442    }
443   
444    int numAdded = 0;
445    int numRemoved = 0;
446    if ("union".equals(mergeType))
447    {
448      if ("union".equals(sourceMerge))
449      {
450        numAdded = rl.addUnion(sourceLists);
451      }
452      else if ("intersection".equals(sourceMerge))
453      {
454        numAdded = rl.addIntersection(sourceLists);
455      }
456      message = numAdded + " reporters added to this list";
457    }
458    else if ("intersection".equals(mergeType))
459    {
460      if ("union".equals(sourceMerge))
461      {
462        numRemoved = rl.retainUnion(sourceLists);
463      }
464      else if ("intersection".equals(sourceMerge))
465      {
466        numRemoved = rl.retainIntersection(sourceLists);
467      }
468      message = numRemoved + " reporters removed from this list";
469    }
470    else if ("complement".equals(mergeType))
471    {
472      if ("union".equals(sourceMerge))
473      {
474        numRemoved = rl.removeUnion(sourceLists);
475      }
476      else if ("intersection".equals(sourceMerge))
477      {
478        numRemoved = rl.removeIntersection(sourceLists);
479      }
480      message = numRemoved + " reporters removed from this list";
481    }
482    dc.commit();
483  }
484  else
485  {
486    throw new WebException("popup", "Invalid command", "The command {1} is not recognised as a valid command.", cmd);
487  }
488}
489finally
490{
491  if (dc != null) dc.close();
492}
493
494if (forward != null)
495{
496  pageContext.forward(forward);
497}
498else if (redirect != null)
499{
500  response.sendRedirect(redirect);
501}
502else if (message == null)
503{
504  response.sendRedirect(root + "common/close_popup.jsp?refresh_opener=1&wait=0");
505}
506else
507{
508  response.sendRedirect(root + "common/close_popup.jsp?refresh_opener=1&message="+HTML.urlEncode(message));
509}
510%>
511
Note: See TracBrowser for help on using the repository browser.