r1282 - in trunk: wikitty-api/src/main/java/org/nuiton/wikitty wikitty-api/src/main/java/org/nuiton/wikitty/query wikitty-api/src/main/java/org/nuiton/wikitty/query/conditions wikitty-api/src/main/java/org/nuiton/wikitty/storage wikitty-solr/src/test/java/org/nuiton/wikitty/storage/solr
Author: bpoussin Date: 2012-01-05 12:05:28 +0100 (Thu, 05 Jan 2012) New Revision: 1282 Url: http://nuiton.org/repositories/revision/wikitty/1282 Log: - ajout du support d'agregat dans le select (AVG, COUNT, MAX, MIN, SUM) ex: select max Person.age Added: trunk/wikitty-api/src/main/java/org/nuiton/wikitty/query/conditions/Aggregate.java Modified: trunk/wikitty-api/src/main/java/org/nuiton/wikitty/WikittyUtil.java trunk/wikitty-api/src/main/java/org/nuiton/wikitty/query/WikittyQueryMaker.java trunk/wikitty-api/src/main/java/org/nuiton/wikitty/query/WikittyQueryParser.java trunk/wikitty-api/src/main/java/org/nuiton/wikitty/query/WikittyQueryResult.java trunk/wikitty-api/src/main/java/org/nuiton/wikitty/query/WikittyQueryVisitorCopy.java trunk/wikitty-api/src/main/java/org/nuiton/wikitty/query/WikittyQueryVisitorToString.java trunk/wikitty-api/src/main/java/org/nuiton/wikitty/query/conditions/Select.java trunk/wikitty-api/src/main/java/org/nuiton/wikitty/storage/WikittySearchEngineHelper.java trunk/wikitty-solr/src/test/java/org/nuiton/wikitty/storage/solr/WikittySearchEngineSolrForQueryTest.java Modified: trunk/wikitty-api/src/main/java/org/nuiton/wikitty/WikittyUtil.java =================================================================== --- trunk/wikitty-api/src/main/java/org/nuiton/wikitty/WikittyUtil.java 2012-01-05 01:39:15 UTC (rev 1281) +++ trunk/wikitty-api/src/main/java/org/nuiton/wikitty/WikittyUtil.java 2012-01-05 11:05:28 UTC (rev 1282) @@ -117,6 +117,7 @@ new SimpleDateFormat().toPattern(), "dd/MM/yy", "dd/MM/yy hh:mm", + "yyyyMMdd", }; public static final String FQ_FIELD_NAME_SEPARATOR = "."; Modified: trunk/wikitty-api/src/main/java/org/nuiton/wikitty/query/WikittyQueryMaker.java =================================================================== --- trunk/wikitty-api/src/main/java/org/nuiton/wikitty/query/WikittyQueryMaker.java 2012-01-05 01:39:15 UTC (rev 1281) +++ trunk/wikitty-api/src/main/java/org/nuiton/wikitty/query/WikittyQueryMaker.java 2012-01-05 11:05:28 UTC (rev 1282) @@ -14,6 +14,7 @@ import org.nuiton.wikitty.entities.BusinessEntityImpl; import org.nuiton.wikitty.entities.FieldType; import org.nuiton.wikitty.entities.Wikitty; +import org.nuiton.wikitty.query.conditions.Aggregate; import org.nuiton.wikitty.query.conditions.And; import org.nuiton.wikitty.query.conditions.Between; import org.nuiton.wikitty.query.conditions.Condition; @@ -851,7 +852,7 @@ } /** - * Add {@link Select}, this condition must be first or + * Add {@link Select}, this condition must be first or * @param element le champs dont il faut extraire les donnees * @return {@code this} * @see {@link Select} @@ -860,11 +861,28 @@ return select(new ElementField(element)); } + /** + * Add {@link Select}, this condition must be first or + * @param element le champs dont il faut extraire les donnees + * @return {@code this} + * @see {@link Select} + */ + public WikittyQueryMaker select(String element, Aggregate aggregate) { + return select(new ElementField(element), aggregate); + } + /* * @see {@link Select} */ public WikittyQueryMaker select(ElementField element) { - Condition child = new Select(element); + return select(element, null); + } + + /* + * @see {@link Select} + */ + public WikittyQueryMaker select(ElementField element, Aggregate aggregate) { + Condition child = new Select(element, aggregate); addOnStack(child); return this; } Modified: trunk/wikitty-api/src/main/java/org/nuiton/wikitty/query/WikittyQueryParser.java =================================================================== --- trunk/wikitty-api/src/main/java/org/nuiton/wikitty/query/WikittyQueryParser.java 2012-01-05 01:39:15 UTC (rev 1281) +++ trunk/wikitty-api/src/main/java/org/nuiton/wikitty/query/WikittyQueryParser.java 2012-01-05 11:05:28 UTC (rev 1282) @@ -7,13 +7,13 @@ import org.apache.commons.lang.StringUtils; import org.apache.commons.logging.Log; import org.apache.commons.logging.LogFactory; +import org.nuiton.wikitty.query.conditions.Aggregate; import org.nuiton.wikitty.query.conditions.And; import org.nuiton.wikitty.query.conditions.Between; import org.nuiton.wikitty.query.conditions.Condition; import org.nuiton.wikitty.query.conditions.ContainsAll; import org.nuiton.wikitty.query.conditions.ContainsOne; import org.nuiton.wikitty.query.conditions.Element; -import org.nuiton.wikitty.query.conditions.ElementExtension; import org.nuiton.wikitty.query.conditions.ElementField; import org.nuiton.wikitty.query.conditions.Equals; import org.nuiton.wikitty.query.conditions.False; @@ -59,7 +59,12 @@ */ @BuildParseTree public class WikittyQueryParser extends BaseParser<Object> { - + + public static final String AVG = "AVG"; + public static final String COUNT = "COUNT"; + public static final String MAX = "MAX"; + public static final String MIN = "MIN"; + public static final String SUM = "SUM"; public static final String SELECT = "SELECT"; public static final String WHERE = "WHERE"; public static final String IN = "IN"; @@ -93,6 +98,11 @@ public Rule icNOT = IgnoreCase(NOT); public Rule icAND = IgnoreCase(AND); public Rule icOR = IgnoreCase(OR); + public Rule icAVG = IgnoreCase(AVG); + public Rule icCOUNT = IgnoreCase(COUNT); + public Rule icMAX = IgnoreCase(MAX); + public Rule icMIN = IgnoreCase(MIN); + public Rule icSUM = IgnoreCase(SUM); public Rule icSELECT = IgnoreCase(SELECT); public Rule icWHERE = IgnoreCase(WHERE); public Rule icIN = IgnoreCase(IN); @@ -355,9 +365,34 @@ push(new In(toElement(pop(1).toString()), (Condition)pop()))); } Rule select() { - return Sequence(icSELECT, space(), field(), push(match()), space(), icWHERE, space(), term(), - push(new Select(new ElementField(pop(1).toString()), (Condition)pop()))); + Var<Aggregate> aggregate = new Var<Aggregate>(); + return Sequence(icSELECT, space(), Optional(aggregate(aggregate)), space(), field(), push(match()), space(), icWHERE, space(), term(), + push(new Select(new ElementField(pop(1).toString()), aggregate.get(), (Condition)pop()))); } + Rule aggregate(Var<Aggregate> aggregate) { + return FirstOf(avg(aggregate), count(aggregate), max(aggregate), min(aggregate), sum(aggregate)); + } + + Rule avg(Var<Aggregate> aggregate) { + return Sequence(icAVG, aggregate.set(Aggregate.AVG)); + } + + Rule count(Var<Aggregate> aggregate) { + return Sequence(icCOUNT, aggregate.set(Aggregate.COUNT)); + } + + Rule max(Var<Aggregate> aggregate) { + return Sequence(icMAX, aggregate.set(Aggregate.MAX)); + } + + Rule min(Var<Aggregate> aggregate) { + return Sequence(icMIN, aggregate.set(Aggregate.MIN)); + } + + Rule sum(Var<Aggregate> aggregate) { + return Sequence(icSUM, aggregate.set(Aggregate.SUM)); + } + Rule keyword() { return Sequence(value(), push(new Keyword(removeQuote(match())))); } Modified: trunk/wikitty-api/src/main/java/org/nuiton/wikitty/query/WikittyQueryResult.java =================================================================== --- trunk/wikitty-api/src/main/java/org/nuiton/wikitty/query/WikittyQueryResult.java 2012-01-05 01:39:15 UTC (rev 1281) +++ trunk/wikitty-api/src/main/java/org/nuiton/wikitty/query/WikittyQueryResult.java 2012-01-05 11:05:28 UTC (rev 1282) @@ -32,7 +32,7 @@ /** nom du critere qui a ete uitilise (peut-etre null) si le critete n'avait pas de nom */ protected String queryName; - /** indice element in global search result */ + /** indice of first result in global search result */ protected int first; /** total number of result if we call the query for all possible result */ protected int totalResult; Modified: trunk/wikitty-api/src/main/java/org/nuiton/wikitty/query/WikittyQueryVisitorCopy.java =================================================================== --- trunk/wikitty-api/src/main/java/org/nuiton/wikitty/query/WikittyQueryVisitorCopy.java 2012-01-05 01:39:15 UTC (rev 1281) +++ trunk/wikitty-api/src/main/java/org/nuiton/wikitty/query/WikittyQueryVisitorCopy.java 2012-01-05 11:05:28 UTC (rev 1282) @@ -123,7 +123,7 @@ @Override public boolean visitEnter(Select o) { - getQueryMaker().select(o.getElement()); + getQueryMaker().select(o.getElement(), o.getAggregate()); return true; } Modified: trunk/wikitty-api/src/main/java/org/nuiton/wikitty/query/WikittyQueryVisitorToString.java =================================================================== --- trunk/wikitty-api/src/main/java/org/nuiton/wikitty/query/WikittyQueryVisitorToString.java 2012-01-05 01:39:15 UTC (rev 1281) +++ trunk/wikitty-api/src/main/java/org/nuiton/wikitty/query/WikittyQueryVisitorToString.java 2012-01-05 11:05:28 UTC (rev 1282) @@ -91,7 +91,11 @@ @Override public boolean visitEnter(Select o) { - text += WikittyQueryParser.SELECT + " " + o.getElement().getValue() + String agg = ""; + if (o.getAggregate() != null) { + agg = o.getAggregate().name() + " "; + } + text += WikittyQueryParser.SELECT + " " + agg + o.getElement().getValue() + " " + WikittyQueryParser.WHERE + " " + WikittyQueryParser.BRACKET_OPEN; return true; } Added: trunk/wikitty-api/src/main/java/org/nuiton/wikitty/query/conditions/Aggregate.java =================================================================== --- trunk/wikitty-api/src/main/java/org/nuiton/wikitty/query/conditions/Aggregate.java (rev 0) +++ trunk/wikitty-api/src/main/java/org/nuiton/wikitty/query/conditions/Aggregate.java 2012-01-05 11:05:28 UTC (rev 1282) @@ -0,0 +1,14 @@ +package org.nuiton.wikitty.query.conditions; + +/** + * Use to aggregate result in Select condition. + * + * @author poussin + * @version $Revision$ + * + * Last update: $Date$ + * by : $Author$ + */ +public enum Aggregate { + AVG, COUNT, MAX, MIN, SUM +} Modified: trunk/wikitty-api/src/main/java/org/nuiton/wikitty/query/conditions/Select.java =================================================================== --- trunk/wikitty-api/src/main/java/org/nuiton/wikitty/query/conditions/Select.java 2012-01-05 01:39:15 UTC (rev 1281) +++ trunk/wikitty-api/src/main/java/org/nuiton/wikitty/query/conditions/Select.java 2012-01-05 11:05:28 UTC (rev 1282) @@ -1,8 +1,6 @@ package org.nuiton.wikitty.query.conditions; import org.apache.commons.lang.ObjectUtils; -import org.apache.commons.logging.Log; -import org.apache.commons.logging.LogFactory; /** * Cette condition n'accept pas d'etre utilise dans une autre condition. @@ -27,17 +25,33 @@ private static final long serialVersionUID = 1L; + protected Aggregate aggregate; protected ElementField element; public Select(ElementField element) { this.element = element; } + public Select(ElementField element, Aggregate aggregate) { + this.element = element; + this.aggregate = aggregate; + } + public Select(ElementField element, Condition restriction) { super(restriction); this.element = element; } + public Select(ElementField element, Aggregate aggregate, Condition restriction) { + super(restriction); + this.element = element; + this.aggregate = aggregate; + } + + public Aggregate getAggregate() { + return aggregate; + } + public ElementField getElement() { return element; } Modified: trunk/wikitty-api/src/main/java/org/nuiton/wikitty/storage/WikittySearchEngineHelper.java =================================================================== --- trunk/wikitty-api/src/main/java/org/nuiton/wikitty/storage/WikittySearchEngineHelper.java 2012-01-05 01:39:15 UTC (rev 1281) +++ trunk/wikitty-api/src/main/java/org/nuiton/wikitty/storage/WikittySearchEngineHelper.java 2012-01-05 11:05:28 UTC (rev 1282) @@ -1,10 +1,14 @@ package org.nuiton.wikitty.storage; +import java.math.BigDecimal; +import java.math.MathContext; import java.util.ArrayList; +import java.util.Collections; import java.util.List; import org.apache.commons.logging.Log; import org.apache.commons.logging.LogFactory; import org.nuiton.wikitty.WikittyException; +import org.nuiton.wikitty.WikittyUtil; import org.nuiton.wikitty.entities.WikittyExtension; import org.nuiton.wikitty.query.FacetSortType; import org.nuiton.wikitty.query.FacetTopic; @@ -66,10 +70,13 @@ // on part de facet qui a deja la bonne condition WikittyQuery querySelect = queryFacet.copy(); querySelect.setLimit(0); + // ne surtout pas mettre 0, sinon toutes les valeurs possibles sont + // retournee pas seulement celle qui satisfont la contrainte querySelect.setFacetMinCount(1); querySelect.setFacetLimit(Integer.MAX_VALUE); // on force le sort pour toujours utiliser le meme - querySelect.setFacetSort(FacetSortType.count); + querySelect.setFacetSort(FacetSortType.name); + // on supprime toutes les facettes, et on ajoute la notre querySelect.setFacetQuery(); querySelect.setFacetField(select.getElement()); @@ -82,13 +89,89 @@ // creation des resultats via la facette select List<FacetTopic> topics = resultSelect.getFacets().get(select.getElement().getValue()); - List<String> selectList = new ArrayList<String>( - Math.min(topics.size(), query.getLimit())); + List<String> selectList = new ArrayList<String>(topics.size()); if (query.getFirst() < topics.size()) { + // il faut que le premier demande soit inferieur a la taille, + // sinon on ne fait rien + for (FacetTopic topic : topics) { + selectList.add(topic.getTopicName()); + } + } + + boolean sortDesc = query.getSortDescending().contains(select.getElement()); + // tri selon l'ordre demande + if (sortDesc) { + // par defaut la facette est deja trie par ordre alphabetique + // donc il n'y a qu'a l'inverser pour avoir l'ordre inverse + Collections.reverse(selectList); + } + + // on ne garde que ce qui est demande + if (query.getFirst() < topics.size()) { int first = query.getFirst(); int last = Math.min(topics.size(), query.getFirst() + query.getLimit()); - for (FacetTopic topic : topics.subList(first, last)) { - selectList.add(topic.getTopicName()); + selectList = selectList.subList(first, last); + } + + // gestion des agregats + if (select.getAggregate() != null) { + switch(select.getAggregate()) { + case AVG: { + // convert all to number + BigDecimal result = new BigDecimal(0); + for (String s : selectList) { + BigDecimal v = WikittyUtil.toBigDecimal(s); + result = result.add(v); + } + result = result.divide(new BigDecimal(selectList.size())); + selectList = new ArrayList<String>(); + selectList.add(WikittyUtil.toString(result)); + } + break; + case COUNT: { + // convert all to number + BigDecimal result = new BigDecimal(selectList.size()); + selectList = new ArrayList<String>(); + selectList.add(WikittyUtil.toString(result)); + } + break; + case MAX: { + if (!selectList.isEmpty()) { + String result; + if (sortDesc) { + result = selectList.get(0); + } else { + result = selectList.get(selectList.size()-1); + } + selectList = new ArrayList<String>(); + selectList.add(WikittyUtil.toString(result)); + } + } + break; + case MIN: { + if (!selectList.isEmpty()) { + String result; + if (sortDesc) { + result = selectList.get(selectList.size()-1); + } else { + result = selectList.get(0); + } + selectList = new ArrayList<String>(); + selectList.add(WikittyUtil.toString(result)); + } + } + break; + case SUM: { + // convert all to number + BigDecimal result = new BigDecimal(0); + for (String s : selectList) { + BigDecimal v = WikittyUtil.toBigDecimal(s); + result = result.add(v); + } + selectList = new ArrayList<String>(); + selectList.add(WikittyUtil.toString(result)); + } + break; } } Modified: trunk/wikitty-solr/src/test/java/org/nuiton/wikitty/storage/solr/WikittySearchEngineSolrForQueryTest.java =================================================================== --- trunk/wikitty-solr/src/test/java/org/nuiton/wikitty/storage/solr/WikittySearchEngineSolrForQueryTest.java 2012-01-05 01:39:15 UTC (rev 1281) +++ trunk/wikitty-solr/src/test/java/org/nuiton/wikitty/storage/solr/WikittySearchEngineSolrForQueryTest.java 2012-01-05 11:05:28 UTC (rev 1282) @@ -1,6 +1,10 @@ package org.nuiton.wikitty.storage.solr; +import java.util.Calendar; import java.util.Collections; +import java.util.Date; +import java.util.List; +import org.apache.commons.lang.time.DateUtils; import org.apache.commons.logging.Log; import org.apache.commons.logging.LogFactory; import org.junit.Assert; @@ -10,6 +14,9 @@ import org.nuiton.util.ApplicationConfig; import org.nuiton.wikitty.WikittyClient; import org.nuiton.wikitty.WikittyConfig; +import org.nuiton.wikitty.WikittyUtil; +import org.nuiton.wikitty.entities.Wikitty; +import org.nuiton.wikitty.entities.WikittyExtension; import org.nuiton.wikitty.entities.WikittyGroup; import org.nuiton.wikitty.entities.WikittyGroupImpl; import org.nuiton.wikitty.entities.WikittyImpl; @@ -21,7 +28,9 @@ import org.nuiton.wikitty.query.WikittyQueryMaker; import org.nuiton.wikitty.query.WikittyQueryParser; import org.nuiton.wikitty.query.WikittyQueryResult; +import org.nuiton.wikitty.query.conditions.Aggregate; import org.nuiton.wikitty.query.conditions.Element; +import org.nuiton.wikitty.query.conditions.ElementField; /** * @@ -57,8 +66,116 @@ } @Test + public void testSelectSortAndAggregate() throws Exception { + WikittyExtension ext = new WikittyExtension("Test", + "1.0", // version + WikittyUtil.tagValuesToMap(" version=\"1.0\""), // tag/values + (List)null, + WikittyUtil.buildFieldMapExtension( // building field map + "Numeric number", + "String string", + "Date date")); + Wikitty w1 = new WikittyImpl(); + w1.addExtension(ext); + w1.setField("Test", "number", 5); + w1.setField("Test", "date", WikittyUtil.parseDate("02/05/1975")); + + Wikitty w2 = new WikittyImpl(); + w2.addExtension(ext); + w2.setField("Test", "number", -4); + w2.setField("Test", "date", WikittyUtil.parseDate("19830606")); + + Wikitty w3 = new WikittyImpl(); + w3.addExtension(ext); + w3.setField("Test", "number", 10); + w3.setField("Test", "date", WikittyUtil.parseDate("21/05/2002")); + + Wikitty w4 = new WikittyImpl(); + w4.addExtension(ext); + w4.setField("Test", "number", 1); + w4.setField("Test", "date", WikittyUtil.parseDate("05/01/2012")); + + client.store(w1, w2, w3, w4); + + { + WikittyQuery q = new WikittyQueryMaker().select("Test.number").end(); + WikittyQueryResult<String> result = client.findAllByQuery(q); + System.out.println("q: " + result); + } + { + WikittyQuery q = new WikittyQueryMaker().select("Test.number").end() + .addSortDescending(new ElementField("Test", "number")); + WikittyQueryResult<String> result = client.findAllByQuery(q); + System.out.println("q: " + result); + } + { + WikittyQuery q = new WikittyQueryMaker().select("Test.date").end(); + WikittyQueryResult<String> result = client.findAllByQuery(q); + System.out.println("q: " + result); + } + { + WikittyQuery q = new WikittyQueryMaker().select("Test.date").end() + .addSortDescending(new ElementField("Test", "date")); + WikittyQueryResult<String> result = client.findAllByQuery(q); + System.out.println("q: " + result); + } + + // test aggregate + { + WikittyQuery q = new WikittyQueryMaker().select("Test.number", Aggregate.AVG).end(); + WikittyQueryResult<String> result = client.findAllByQuery(q); + System.out.println("q: " + result); + Assert.assertEquals("3.0", result.peek()); + } + { + WikittyQuery q = new WikittyQueryMaker().select("Test.number", Aggregate.COUNT).end(); + WikittyQueryResult<String> result = client.findAllByQuery(q); + System.out.println("q: " + result); + Assert.assertEquals("4", result.peek()); + } + { + WikittyQuery q = new WikittyQueryMaker().select("Test.number", Aggregate.MAX).end(); + WikittyQueryResult<String> result = client.findAllByQuery(q); + System.out.println("q: " + result); + Assert.assertEquals("10.0", result.peek()); + } + { + WikittyQuery q = new WikittyQueryMaker().select("Test.number", Aggregate.MAX).end() + .addSortDescending(new ElementField("Test", "number")); + WikittyQueryResult<String> result = client.findAllByQuery(q); + System.out.println("q: " + result); + Assert.assertEquals("10.0", result.peek()); + } + { + WikittyQuery q = new WikittyQueryMaker().select("Test.number", Aggregate.MIN).end(); + WikittyQueryResult<String> result = client.findAllByQuery(q); + System.out.println("q: " + result); + Assert.assertEquals("-4.0", result.peek()); + } + { + WikittyQuery q = new WikittyQueryMaker().select("Test.number", Aggregate.MIN).end() + .addSortDescending(new ElementField("Test", "number")); + WikittyQueryResult<String> result = client.findAllByQuery(q); + System.out.println("q: " + result); + Assert.assertEquals("-4.0", result.peek()); + } + { + WikittyQuery q = new WikittyQueryMaker().select("Test.number", Aggregate.SUM).end(); + WikittyQueryResult<String> result = client.findAllByQuery(q); + System.out.println("q: " + result); + Assert.assertEquals("12.0", result.peek()); + } + + } + + @Test + public void testSelectAggregate() throws Exception { + + } + + @Test public void testFacet() throws Exception { - // for id for easy debugging + // for id for easy debugging WikittyGroupImpl g1 = new WikittyGroupImpl(new WikittyImpl("g1")); g1.setName("Group1"); WikittyGroupImpl g2 = new WikittyGroupImpl(new WikittyImpl("g2"));
participants (1)
-
bpoussin＠users.nuiton.org