Many Thanks Pontus! :)
Miguel -----Original Message----- From: Pontus Strand [mailto:[EMAIL PROTECTED] Sent: terça-feira, 18 de Janeiro de 2005 13:17 To: 'Slide Users Mailing List' Subject: RE: Slide search performance issues Ok, it seems that zip-files aren't approved by our mailserver ... In the file AbstractRDBMSStore this was added: // TODO: Added method. Maybe a bad idea to expose this variable? public RDBMSAdapter getAdapter() { return adapter; } -------------------------------------------- In the file RDBMSExpressionFactory this method was modified: protected ComparableResourcesPool getRequestedResourcePool() { if (requestedResourcePool == null) { // TODO: Add comment here. // TODO: Add more pools here if need be ... if (_store.getAdapter() instanceof MySql41RDBMSAdapter) { requestedResourcePool = new MySql41ComparableResourcesPool(_store, _context, getQuery()); } else { requestedResourcePool = new RDBMSComparableResourcesPool(_store, _context, getQuery()); } } return requestedResourcePool; } ------------------------------------------------------ The file MySql41ComparableResourcesPool.java was created and looks like this: /* * $Header: /home/cvs/jakarta-slide/src/stores/org/apache/slide/store/impl/rdbms/MySql41 ComparableResourcesPool.java,v 1.10.2.4 2004/10/27 12:58:41 unico Exp $ * $Revision: 1.10.2.4 $ * $Date: 2004/10/27 12:58:41 $ * * ==================================================================== * * Copyright 1999-2004 The Apache Software Foundation * * Licensed under the Apache License, Version 2.0 (the "License"); * you may not use this file except in compliance with the License. * You may obtain a copy of the License at * * http://www.apache.org/licenses/LICENSE-2.0 * * Unless required by applicable law or agreed to in writing, software * distributed under the License is distributed on an "AS IS" BASIS, * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. * See the License for the specific language governing permissions and * limitations under the License. * */ package org.apache.slide.store.impl.rdbms; import java.lang.reflect.Constructor; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.Collections; import java.util.HashMap; import java.util.HashSet; import java.util.Iterator; import java.util.Map; import java.util.Set; import org.apache.slide.common.PropertyName; import org.apache.slide.common.RequestedProperties; import org.apache.slide.common.RequestedProperty; import org.apache.slide.common.RequestedPropertyImpl; import org.apache.slide.common.ServiceAccessException; import org.apache.slide.common.SlideException; import org.apache.slide.common.SlideRuntimeException; import org.apache.slide.content.NodeProperty; import org.apache.slide.search.BadQueryException; import org.apache.slide.search.PropertyProvider; import org.apache.slide.search.QueryScope; import org.apache.slide.search.SearchQuery; import org.apache.slide.search.SearchToken; import org.apache.slide.search.basic.ComparableResourceImpl; import org.apache.slide.search.basic.ComparableResourcesPool; import org.apache.slide.search.basic.IBasicQuery; import org.apache.slide.security.AccessDeniedException; import org.apache.slide.store.impl.rdbms.expression.RDBMSExpressionFactory; import org.apache.slide.store.impl.rdbms.expression.RDBMSQueryContext; import org.apache.slide.structure.ObjectNode; import org.apache.slide.util.logger.Logger; /** */ public class MySql41ComparableResourcesPool extends RDBMSComparableResourcesPool { private final AbstractRDBMSStore _store; private final RDBMSQueryContext _context; private final IBasicQuery _query; private final SearchToken _token; private final QueryScope _scope; private final Map _selectProperties; private final PropertyProvider _provider; private Set _pool; public MySql41ComparableResourcesPool(AbstractRDBMSStore store, RDBMSQueryContext context, IBasicQuery query) { super(store, context, query); _store = store; _context = context; _query = query; _token = _query.getSearchToken(); _scope = _query.getScope(); _selectProperties = new HashMap(); _provider = new RDBMSPropertyProvider(_query.getPropertyProvider(), _selectProperties); if (_query instanceof SearchQuery) { final RequestedProperties props = ((SearchQuery) _query).requestedProperties(); if (!props.isAllProp()) { final Iterator iter = props.getRequestedProperties(); while (iter.hasNext()) { final RequestedProperty property = (RequestedProperty) iter.next(); final String selectKey = property.getNamespace() + property.getName(); if (_context.selects().containsKey(selectKey)) { _selectProperties.put(property, new HashMap()); } } } } } public Iterator resourceIterator() { try { return getPool().iterator(); } catch (BadQueryException e) { throw new SlideRuntimeException(e.toString()); } } public Set getPool() throws BadQueryException { if (_pool == null) { try { // TODO: Fix comments to this if (_query.isLimitDefined()) { int limit = _query.getLimit(); int loopCount = 0; _pool = new HashSet(limit); while (_pool.size() < limit) { ObjectNode[] objects = retrieveObjects(loopCount * limit); for (int i = 0; (i < objects.length) && (_pool.size() < limit); i++) { try { _pool.add(new ComparableResourceImpl(objects[i], _token, _scope, _provider)); } catch (AccessDeniedException e) { // ignore: object is not visible } } // Don't continue loop if the number of returned hits is // below the limit!! if (objects.length < limit) { break; } } } else { super.getPool(); } } catch (ServiceAccessException e) { throw new BadQueryException(e); } catch (SlideException e) { throw new BadQueryException(e); } } return _pool; } public boolean partialResult() { return false; } public QueryScope getScope() { return _scope; } private ObjectNode[] retrieveObjects(int limitOffset) throws ServiceAccessException, BadQueryException { if (_store.getCurrentlyActiveTransactionalResource() == null) { Connection connection = null; try { connection = _store.getNewConnection(); return retrieveObjects(connection, limitOffset); } catch (SQLException e) { throw new ServiceAccessException(_store, e); } finally { if (connection != null) { try { connection.close(); } catch (SQLException e) { _store.getLogger().log(e, AbstractRDBMSStore.LOG_CHANNEL, Logger.WARNING); } } } } else { return retrieveObjects(_store.getCurrentConnection(), limitOffset); } } private ObjectNode[] retrieveObjects(Connection connection, int limitOffset) throws ServiceAccessException, BadQueryException { PreparedStatement statement = null; ResultSet result = null; ArrayList classNames = new ArrayList(); ArrayList uris = new ArrayList(); try { final String sql = compileSQL(limitOffset); if (_store.getLogger().isEnabled(Logger.INFO)) { _store.getLogger().log("executing: " + sql, AbstractRDBMSStore.LOG_CHANNEL, Logger.INFO); } statement = connection.prepareStatement(sql); result = statement.executeQuery(); while (result.next()) { final String uri = result.getString(1); final String className = result.getString(2); uris.add(uri); classNames.add(className); Iterator iter = _selectProperties.keySet().iterator(); while (iter.hasNext()) { final RequestedProperty requested = (RequestedProperty) iter.next(); final String name = requested.getName(); final String namespace = requested.getNamespace(); final String alias = RDBMSExpressionFactory.propertyToAlias(requested.getName()); final String value = result.getString(alias); final NodeProperty property = new NodeProperty(name, value, namespace); final Map properties = (Map) _selectProperties.get(requested); properties.put(uri, property); } } } catch (SQLException e) { throw new ServiceAccessException(_store, e); } finally { if (result != null) { try { result.close(); } catch (SQLException e) { _store.getLogger().log(e, AbstractRDBMSStore.LOG_CHANNEL, Logger.WARNING); } } if (statement != null) { try { statement.close(); } catch (SQLException e) { _store.getLogger().log(e, AbstractRDBMSStore.LOG_CHANNEL, Logger.WARNING); } } } int size = uris.size(); ObjectNode[] objects = new ObjectNode[size]; for (int i = 0; i < size; i++) { try { Class objclass = Class.forName((String) classNames.get(i)); Class argClasses[] = { String.class }; Object arguments[] = { uris.get(i) }; Constructor constructor = objclass.getConstructor(argClasses); objects[i] = (ObjectNode) constructor.newInstance(arguments); objects[i].setUri(objects[i].getUuri()); } catch (Exception e) { throw new ServiceAccessException(_store, e); } } return objects; } private String compileSQL(int limitOffset) throws BadQueryException { String uri = _token.getSlideContext().getSlidePath(_scope.getHref()); if (uri.endsWith("/")) { uri = uri.substring(0, uri.length()-1); } String query = "select " + compileSelect() + " from " + compileJoins() + " where " + compileScope(uri); final String criteria = compileCriteria(); query = (criteria != null && criteria.length() > 0) ? query + " AND " + criteria : query; query = query + " limit " + limitOffset + "," + _query.getLimit(); return query; } private String compileSelect() { String select = "u.URI_STRING, o.CLASS_NAME"; final Iterator iter = _selectProperties.keySet().iterator(); while (iter.hasNext()) { final RequestedProperty property = (RequestedProperty) iter.next(); final String selectKey = property.getNamespace() + property.getName(); String propSelect = (String) _context.selects().get(selectKey); if (propSelect != null) { select += ", " + propSelect; } } return select; } private String compileScope(String uri) { switch (_scope.getDepth()) { case QueryScope.DEPTH_0: { return " u.URI_STRING = '" + uri + "'"; } case QueryScope.DEPTH_1: { return " (u.URI_STRING = '" + uri + "'" + " OR (u.URI_STRING LIKE '" + uri + "/%'" + " AND u.URI_STRING NOT LIKE '" + uri + "/%/%'))"; } case QueryScope.DEPTH_INFINITY: default: { return " (u.URI_STRING = '" + uri + "'" + " OR u.URI_STRING LIKE '" + uri + "/%')"; } } } private String compileCriteria() { String result = null; if (_context.criteria().size() > 0) { result = ""; Iterator iter = _context.criteria().iterator(); while (iter.hasNext()) { result += iter.next(); } } return result; } private String compileJoins() { String joins = "((OBJECT o " + "inner join URI u on u.URI_ID = o.URI_ID) " + "inner join VERSION_HISTORY vh on vh.URI_ID = u.URI_ID) "; Iterator iter = _context.joins().iterator(); while (iter.hasNext()) { joins = "(" + joins + " " + iter.next() + ") "; } return joins; } private static class RDBMSPropertyProvider implements PropertyProvider { private final PropertyProvider _propertyProvider; private final Map _selectProperties; private RDBMSPropertyProvider(PropertyProvider propertyProvider, Map selectProperties) { _propertyProvider = propertyProvider; _selectProperties = selectProperties; } public boolean isSupportedProperty(String resourceUri, String propertyName, String propertyNamespace) throws SlideException { if (_selectProperties.containsKey(new RequestedPropertyImpl(propertyName, propertyNamespace))) { return true; } else if (_propertyProvider != null) { return _propertyProvider.isSupportedProperty(resourceUri, propertyName, propertyNamespace); } return false; } public Iterator getSupportedPropertiesNames(String resourceUri) throws SlideException { Iterator selected = _selectProperties.keySet().iterator(); Iterator provided = null; if (_propertyProvider != null) { provided = _propertyProvider.getSupportedPropertiesNames(resourceUri); } return new PropertyNamesIterator(selected, provided); } public NodeProperty getProperty(String resourceUri, String propertyName, String propertyNamespace) throws SlideException { Map properties = (Map) _selectProperties.get(new RequestedPropertyImpl(propertyName, propertyNamespace)); if (properties != null) { return (NodeProperty) properties.get(resourceUri); } else if (_propertyProvider != null) { return _propertyProvider.getProperty(resourceUri, propertyName, propertyNamespace); } return null; } public Iterator getSupportedProperties(String resourceUri) throws SlideException { Iterator selected = _selectProperties.values().iterator(); Iterator provided = null; if (_propertyProvider != null) { provided = _propertyProvider.getSupportedProperties(resourceUri); } return new NodePropertiesIterator(resourceUri, selected, provided); } private static class PropertyNamesIterator implements Iterator { private final Iterator _selectedProperties; private final Iterator _providedProperties; private PropertyNamesIterator(Iterator selectedProperties, Iterator providedProperties) { _selectedProperties = selectedProperties; if (providedProperties != null) { _providedProperties = providedProperties; } else { _providedProperties = Collections.EMPTY_LIST.iterator(); } } public void remove() { throw new UnsupportedOperationException(); } public boolean hasNext() { return _selectedProperties.hasNext() || _providedProperties.hasNext(); } public Object next() { if (_selectedProperties.hasNext()) { RequestedProperty property = (RequestedProperty) _selectedProperties.next(); return new PropertyName(property.getName(), property.getNamespace()); } return _providedProperties.next(); } } private static class NodePropertiesIterator implements Iterator { private String _resourceUri; private Iterator _selectedProperties; private Iterator _providedProperties; private NodePropertiesIterator(String resourceUri, Iterator selectedProperties, Iterator providedProperties) { _resourceUri = resourceUri; _selectedProperties = selectedProperties; if (providedProperties != null) { _providedProperties = providedProperties; } else { _providedProperties = Collections.EMPTY_LIST.iterator(); } } public void remove() { throw new UnsupportedOperationException(); } public boolean hasNext() { return _selectedProperties.hasNext() || _providedProperties.hasNext(); } public Object next() { if (_selectedProperties.hasNext()) { Map properties = (Map) _selectedProperties.next(); return (NodeProperty) properties.get(_resourceUri); } return _providedProperties.next(); } } } } ------------------------ That's it ... Hopefully this will work ... Best regards, Pontus > -----Original Message----- > From: Pontus Strand [mailto:[EMAIL PROTECTED] > Sent: Tuesday, January 18, 2005 2:06 PM > To: 'Slide Users Mailing List' > Subject: RE: Slide search performance issues > > > I have been asked to post my changes, please note that I > consider this a > quick fix or even a hack. > > The basic idea behind it is that the ComparableResourcesPool used is > specific to each database. In order to solve this the method > getRequestedResourcePool() in RDBMSExpressionFactory was > modified to check > which RDBMS-adapter is used. To achieve that I hade to modify > AbstractRDBMSStore to return the adapter, i.e. I added the method > getAdapter(). I'm not sure this is a good solution, hence my > designation of > this as a hack. > > Finally I added the class MySql41ComparableResourcesPool, that is an > extension of RDBMSComparableResourcesPool. The class is > basically a copy of > RDBMSComparableResourcesPool, but with a few small > differences. The method > getPool() check to see if a limit has been requested, if so > it tries to > retrieve objects matching the limit. Worth noting is that the > SQL-query can > return documents that the user doesn't have access to. So in > order to get > the right amount of documents the SQL-query is asked as many > times as needed > to get either the requested limit or until no more results > can be found. > > The other major method that was changed is compileSQL(), this > method was > modified to include the "limit"-clause. Also, the > retrieveObjects()-method > where modified to take the current starting offset as a parameter, a > parameter that is passed on to compileSQL(). > > I hope this desciption is good enough to understand the > changes made and the > logic behind those changes. Perhaps a seed has been planted > so that someone > can implement a more thought through solution. > > Best regards, > Pontus > > > -----Original Message----- > > From: Pontus Strand [mailto:[EMAIL PROTECTED] > > Sent: Tuesday, January 18, 2005 10:04 AM > > To: 'Slide Users Mailing List' > > Subject: RE: Slide search performance issues > > > > > > So I guess one wouldn't have to have an Exchange Server in order to > > implement those extensions? I'm not too happy about using > proprietary > > extensions to a standard, but sometimes you have to be a > > realist and accept > > things... > > > > Anyway, we have solved to problem by modifying the source to > > Slide. This is > > not a good way to solve the problem, as upgrades to newer > > versions of Slide > > will be difficult but it was necessary to get the > performance we need. > > Basically what we did was to add a MySQL-specific > > ComparableResourcesPool > > that adds the keyword "limit" to the select statement. This > > was a quick fix > > to a serious problem so it not thought through all the way > > yet so maybe we > > will do something else in the future. > > > > Regards, > > Pontus > > > > > -----Original Message----- > > > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > > > Sent: Monday, January 17, 2005 3:37 PM > > > To: 'Slide Users Mailing List' > > > Subject: AW: Slide search performance issues > > > > > > > > > My favourite approach concerning this issue would be to > > implement the > > > MS-Exchange extension that adds the ability to specify to > > > limit the result > > > set by specifying ranges of results. > > > This is described at the MS WebDAV pages as far as I > > > remember. But it is not > > > that easy to implement that on server side. But in fact would > > > lead a big > > > step towards Exchange compatibility... > > > I know that everybody hates MS to extend the standards, but > > > in the area of > > > WebDAV they've added some useful bits. So why not adopt it? > > > Some ranges could be mapped to sql commands by retrieving > > > only the first > > > rows, others could at least be filtered at java result set > > > level. Any ideas? > > > > > > Cheers, > > > Daniel > > > > > > > -----Ursprüngliche Nachricht----- > > > > Von: > > [EMAIL PROTECTED] > > > > > > > [mailto:[EMAIL PROTECTED] > > > pache.org] > > > > Im Auftrag von Pontus Strand > > > > Gesendet: Montag, 17. Januar 2005 08:22 > > > > An: 'Slide Users Mailing List' > > > > Betreff: RE: Slide search performance issues > > > > > > > > Ok, found the reason to why the query below didn't work, I > > > had failed to > > > > include a "group by"-clause. > > > > > > > > However, that doen't solve the problem. If I get lots of > > > hits when doing a > > > > search it still takes a long time to execute. What I would > > > like to do is > > > > to > > > > move the limititation from the Slide server to the > > > DB-server (where it > > > > really belong). As I understand it the keyword "limit" > > isn't part of > > > > SQL-standard (Orcale for one doen't implement it) so I > > > guess that this is > > > > difficult do in generic terms. This is, however, important > > > for us as we > > > > need > > > > the performance so which class/classes would I have to > > > override in order > > > > to > > > > modify the behaviour of the search method? Is this > > > recommended at all? Or > > > > should we try to limit the client so we can't do to > wide searches? > > > > > > > > /Pontus > > > > > > > > > -----Original Message----- > > > > > From: Pontus Strand [mailto:[EMAIL PROTECTED] > > > > > Sent: Friday, January 14, 2005 2:51 PM > > > > > To: 'Slide Users Mailing List' > > > > > Subject: RE: Slide search performance issues > > > > > > > > > > > > > > > A quick follow-up, when working with large number of files > > > > > the number of > > > > > hits when doing a search could be large. As I understand it, > > > > > it is possible > > > > > to limit the number of responses by using the DAV:limit XML > > > > > element from the > > > > > DASL specification. However, the specification also states > > > > > that the server > > > > > may disregard the requested limit. So my questions are: Does > > > > > Slide support > > > > > DAV:limit? And will it work when using the > > > > > "use-rdbms-expression-factory" > > > > > parameter? And, finally, am I using correct syntax in the > > > > > example below? > > > > > > > > > > <D:searchrequest xmlns:D =\"DAV:\"> > > > > > <D:basicsearch> > > > > > <D:select> > > > > > <D:allprop/> > > > > > </D:select> > > > > > <D:from> > > > > > <D:scope> > > > > > <D:href></D:href> > > > > > </D:scope> > > > > > </D:from> > > > > > <D:where> > > > > > <D:and> > > > > > <D:eq><D:prop><D:fileextension/></D:prop> > > > > > <D:literal>xml</D:literal></D:eq> > > > > > </D:and> > > > > > </D:where> > > > > > <D:limit> > > > > > <D:nresults> > > > > > 10 > > > > > </D:nresults> > > > > > </D:limit> > > > > > </D:basicsearch> > > > > > </D:searchrequest> > > > > > > > > > > The reason for my questions is that I can't get it to work > > > > > and I can't find > > > > > any references to this. Is there perhaps another way to limit > > > > > the number of > > > > > search hits? > > > > > > > > > > Regards, > > > > > Pontus > > > > > > > > > > > > > > > > --------------------------------------------------------------------- > > > > > To unsubscribe, e-mail: > > [EMAIL PROTECTED] > > > > > For additional commands, e-mail: > > > [EMAIL PROTECTED] > > > > > > > > > > > > > > > > > > > --------------------------------------------------------------------- > > > > To unsubscribe, e-mail: > [EMAIL PROTECTED] > > > > For additional commands, e-mail: > > [EMAIL PROTECTED] > > > > > > > > > > > > --------------------------------------------------------------------- > > > To unsubscribe, e-mail: [EMAIL PROTECTED] > > > For additional commands, e-mail: > [EMAIL PROTECTED] > > > > > > > > --------------------------------------------------------------------- > > To unsubscribe, e-mail: [EMAIL PROTECTED] > > For additional commands, e-mail: [EMAIL PROTECTED] > > > > > --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
