@Override public Set<Pair<Long, Long>> searchTemplates( String name, String keyword, TemplateFilter templateFilter, boolean isIso, List<HypervisorType> hypers, Boolean bootable, DomainVO domain, Long pageSize, Long startIndex, Long zoneId, HypervisorType hyperType, boolean onlyReady, boolean showDomr, List<Account> permittedAccounts, Account caller, ListProjectResourcesCriteria listProjectResourcesCriteria) { StringBuilder builder = new StringBuilder(); if (!permittedAccounts.isEmpty()) { for (Account permittedAccount : permittedAccounts) { builder.append(permittedAccount.getAccountId() + ","); } } String permittedAccountsStr = builder.toString(); if (permittedAccountsStr.length() > 0) { // chop the "," off permittedAccountsStr = permittedAccountsStr.substring(0, permittedAccountsStr.length() - 1); } Transaction txn = Transaction.currentTxn(); txn.start(); /* Use LinkedHashSet here to guarantee iteration order */ Set<Pair<Long, Long>> templateZonePairList = new LinkedHashSet<Pair<Long, Long>>(); PreparedStatement pstmt = null; ResultSet rs = null; StringBuilder relatedDomainIds = new StringBuilder(); String sql = SELECT_TEMPLATE_ZONE_REF; String groupByClause = ""; try { // short accountType; // String accountId = null; String guestOSJoin = ""; StringBuilder templateHostRefJoin = new StringBuilder(); String dataCenterJoin = ""; if (isIso && !hyperType.equals(HypervisorType.None)) { guestOSJoin = " INNER JOIN guest_os guestOS on (guestOS.id = t.guest_os_id) INNER JOIN guest_os_hypervisor goh on ( goh.guest_os_id = guestOS.id) "; } if (onlyReady) { templateHostRefJoin.append( " INNER JOIN template_host_ref thr on (t.id = thr.template_id) INNER JOIN host h on (thr.host_id = h.id)"); sql = SELECT_TEMPLATE_HOST_REF; groupByClause = " GROUP BY t.id, h.data_center_id "; } if ((templateFilter == TemplateFilter.featured) || (templateFilter == TemplateFilter.community)) { dataCenterJoin = " INNER JOIN data_center dc on (h.data_center_id = dc.id)"; } sql += guestOSJoin + templateHostRefJoin + dataCenterJoin; String whereClause = ""; // All joins have to be made before we start setting the condition settings if ((listProjectResourcesCriteria == ListProjectResourcesCriteria.SkipProjectResources || (!permittedAccounts.isEmpty() && !(templateFilter == TemplateFilter.community || templateFilter == TemplateFilter.featured))) && !(caller.getType() != Account.ACCOUNT_TYPE_NORMAL && templateFilter == TemplateFilter.all)) { whereClause += " INNER JOIN account a on (t.account_id = a.id)"; if ((templateFilter == TemplateFilter.self || templateFilter == TemplateFilter.selfexecutable) && (caller.getType() == Account.ACCOUNT_TYPE_DOMAIN_ADMIN || caller.getType() == Account.ACCOUNT_TYPE_RESOURCE_DOMAIN_ADMIN)) { whereClause += " INNER JOIN domain d on (a.domain_id = d.id) WHERE d.path LIKE '" + domain.getPath() + "%'"; if (listProjectResourcesCriteria == ListProjectResourcesCriteria.SkipProjectResources) { whereClause += " AND a.type != " + Account.ACCOUNT_TYPE_PROJECT; } } else if (listProjectResourcesCriteria == ListProjectResourcesCriteria.SkipProjectResources) { whereClause += " WHERE a.type != " + Account.ACCOUNT_TYPE_PROJECT; } } if (!permittedAccounts.isEmpty()) { for (Account account : permittedAccounts) { // accountType = account.getType(); // accountId = Long.toString(account.getId()); DomainVO accountDomain = _domainDao.findById(account.getDomainId()); // get all parent domain ID's all the way till root domain DomainVO domainTreeNode = accountDomain; while (true) { relatedDomainIds.append(domainTreeNode.getId()); relatedDomainIds.append(","); if (domainTreeNode.getParent() != null) { domainTreeNode = _domainDao.findById(domainTreeNode.getParent()); } else { break; } } // get all child domain ID's if (isAdmin(account.getType())) { List<DomainVO> allChildDomains = _domainDao.findAllChildren(accountDomain.getPath(), accountDomain.getId()); for (DomainVO childDomain : allChildDomains) { relatedDomainIds.append(childDomain.getId()); relatedDomainIds.append(","); } } relatedDomainIds.setLength(relatedDomainIds.length() - 1); } } String attr = " AND "; if (whereClause.endsWith(" WHERE ")) { attr += " WHERE "; } if (!isIso) { if (hypers.isEmpty()) { return templateZonePairList; } else { StringBuilder relatedHypers = new StringBuilder(); for (HypervisorType hyper : hypers) { relatedHypers.append("'"); relatedHypers.append(hyper.toString()); relatedHypers.append("'"); relatedHypers.append(","); } relatedHypers.setLength(relatedHypers.length() - 1); whereClause += attr + " t.hypervisor_type IN (" + relatedHypers + ")"; } } if (!permittedAccounts.isEmpty() && !(templateFilter == TemplateFilter.featured || templateFilter == TemplateFilter.community) && !isAdmin(caller.getType())) { whereClause += attr + "t.account_id IN (" + permittedAccountsStr + ")"; } if (templateFilter == TemplateFilter.featured) { whereClause += attr + "t.public = 1 AND t.featured = 1"; if (!permittedAccounts.isEmpty()) { whereClause += attr + "(dc.domain_id IN (" + relatedDomainIds + ") OR dc.domain_id is NULL)"; } } else if (templateFilter == TemplateFilter.self || templateFilter == TemplateFilter.selfexecutable) { whereClause += " AND t.account_id IN (" + permittedAccountsStr + ")"; } else if (templateFilter == TemplateFilter.sharedexecutable) { whereClause += " LEFT JOIN launch_permission lp ON t.id = lp.template_id WHERE" + " (t.account_id IN (" + permittedAccountsStr + ") OR" + " lp.account_id IN (" + permittedAccountsStr + "))"; } else if (templateFilter == TemplateFilter.executable && !permittedAccounts.isEmpty()) { whereClause += attr + "(t.public = 1 OR t.account_id IN (" + permittedAccountsStr + "))"; } else if (templateFilter == TemplateFilter.community) { whereClause += attr + "t.public = 1 AND t.featured = 0"; if (!permittedAccounts.isEmpty()) { whereClause += attr + "(dc.domain_id IN (" + relatedDomainIds + ") OR dc.domain_id is NULL)"; } } else if (caller.getType() != Account.ACCOUNT_TYPE_ADMIN && !isIso) { return templateZonePairList; } if (whereClause.equals("")) { whereClause += " WHERE "; } else if (!whereClause.equals(" WHERE ")) { whereClause += " AND "; } sql += whereClause + getExtrasWhere( templateFilter, name, keyword, isIso, bootable, hyperType, zoneId, onlyReady, showDomr) + groupByClause + getOrderByLimit(pageSize, startIndex); pstmt = txn.prepareStatement(sql); rs = pstmt.executeQuery(); while (rs.next()) { Pair<Long, Long> templateZonePair = new Pair<Long, Long>(rs.getLong(1), rs.getLong(2)); templateZonePairList.add(templateZonePair); } // for now, defaulting pageSize to a large val if null; may need to revisit post 2.2RC2 if (isIso && templateZonePairList.size() < (pageSize != null ? pageSize : 500) && templateFilter != TemplateFilter.community && !(templateFilter == TemplateFilter.self && !BaseCmd.isRootAdmin( caller.getType()))) { // evaluates to true If root admin and filter=self List<VMTemplateVO> publicIsos = publicIsoSearch(bootable, false); for (int i = 0; i < publicIsos.size(); i++) { if (keyword != null && publicIsos.get(i).getName().contains(keyword)) { templateZonePairList.add(new Pair<Long, Long>(publicIsos.get(i).getId(), null)); continue; } else if (name != null && publicIsos.get(i).getName().contains(name)) { templateZonePairList.add(new Pair<Long, Long>(publicIsos.get(i).getId(), null)); continue; } else if (keyword == null && name == null) { templateZonePairList.add(new Pair<Long, Long>(publicIsos.get(i).getId(), null)); } } } } catch (Exception e) { s_logger.warn("Error listing templates", e); } finally { try { if (rs != null) { rs.close(); } if (pstmt != null) { pstmt.close(); } txn.commit(); } catch (SQLException sqle) { s_logger.warn("Error in cleaning up", sqle); } } return templateZonePairList; }