private String getExtrasWhere( TemplateFilter templateFilter, String name, String keyword, boolean isIso, Boolean bootable, HypervisorType hyperType, Long zoneId, boolean onlyReady, boolean showDomr) { String sql = ""; if (keyword != null) { sql += " t.name LIKE \"%" + keyword + "%\" AND"; } else if (name != null) { sql += " t.name LIKE \"%" + name + "%\" AND"; } if (isIso) { sql += " t.format = 'ISO'"; if (!hyperType.equals(HypervisorType.None)) { sql += " AND goh.hypervisor_type = '" + hyperType.toString() + "'"; } } else { sql += " t.format <> 'ISO'"; if (!hyperType.equals(HypervisorType.None)) { sql += " AND t.hypervisor_type = '" + hyperType.toString() + "'"; } } if (bootable != null) { sql += " AND t.bootable = " + bootable; } if (onlyReady) { sql += " AND thr.download_state = '" + Status.DOWNLOADED.toString() + "'" + " AND thr.destroyed=0 "; if (zoneId != null) { sql += " AND h.data_center_id = " + zoneId; } } else if (zoneId != null) { sql += " AND tzr.zone_id = " + zoneId + " AND tzr.removed is null"; } else { sql += " AND tzr.removed is null "; } if (!showDomr) { sql += " AND t.type != '" + Storage.TemplateType.SYSTEM.toString() + "'"; } sql += " AND t.removed IS NULL"; return sql; }
@Override public Set<Pair<Long, Long>> searchSwiftTemplates( 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) { 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(); Set<Pair<Long, Long>> templateZonePairList = new HashSet<Pair<Long, Long>>(); PreparedStatement pstmt = null; ResultSet rs = null; String sql = SELECT_TEMPLATE_SWIFT_REF; try { String joinClause = ""; String whereClause = " WHERE t.removed IS NULL"; if (isIso) { whereClause += " AND t.format = 'ISO'"; if (!hyperType.equals(HypervisorType.None)) { joinClause = " 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) "; whereClause += " AND goh.hypervisor_type = '" + hyperType.toString() + "'"; } } else { whereClause += " AND t.format <> 'ISO'"; 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 += " AND t.hypervisor_type IN (" + relatedHypers + ")"; } } joinClause += " INNER JOIN template_swift_ref tsr on (t.id = tsr.template_id)"; if (keyword != null) { whereClause += " AND t.name LIKE \"%" + keyword + "%\""; } else if (name != null) { whereClause += " AND t.name LIKE \"%" + name + "%\""; } if (bootable != null) { whereClause += " AND t.bootable = " + bootable; } if (!showDomr) { whereClause += " AND t.type != '" + Storage.TemplateType.SYSTEM.toString() + "'"; } if (templateFilter == TemplateFilter.featured) { whereClause += " AND t.public = 1 AND t.featured = 1"; } else if ((templateFilter == TemplateFilter.self || templateFilter == TemplateFilter.selfexecutable) && caller.getType() != Account.ACCOUNT_TYPE_ADMIN) { if (caller.getType() == Account.ACCOUNT_TYPE_DOMAIN_ADMIN || caller.getType() == Account.ACCOUNT_TYPE_RESOURCE_DOMAIN_ADMIN) { joinClause += " INNER JOIN account a on (t.account_id = a.id) INNER JOIN domain d on (a.domain_id = d.id)"; whereClause += " AND d.path LIKE '" + domain.getPath() + "%'"; } else { whereClause += " AND t.account_id IN (" + permittedAccountsStr + ")"; } } else if (templateFilter == TemplateFilter.sharedexecutable && caller.getType() != Account.ACCOUNT_TYPE_ADMIN) { if (caller.getType() == Account.ACCOUNT_TYPE_NORMAL) { joinClause += " LEFT JOIN launch_permission lp ON t.id = lp.template_id WHERE" + " (t.account_id IN (" + permittedAccountsStr + ") OR" + " lp.account_id IN (" + permittedAccountsStr + "))"; } else { joinClause += " INNER JOIN account a on (t.account_id = a.id) "; } } else if (templateFilter == TemplateFilter.executable && !permittedAccounts.isEmpty()) { whereClause += " AND (t.public = 1 OR t.account_id IN (" + permittedAccountsStr + "))"; } else if (templateFilter == TemplateFilter.community) { whereClause += " AND t.public = 1 AND t.featured = 0"; } else if (templateFilter == TemplateFilter.all && caller.getType() == Account.ACCOUNT_TYPE_ADMIN) { } else if (caller.getType() != Account.ACCOUNT_TYPE_ADMIN) { return templateZonePairList; } sql += joinClause + whereClause + getOrderByLimit(pageSize, startIndex); pstmt = txn.prepareStatement(sql); rs = pstmt.executeQuery(); while (rs.next()) { Pair<Long, Long> templateZonePair = new Pair<Long, Long>(rs.getLong(1), -1L); templateZonePairList.add(templateZonePair); } } 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; }