SQLQueryBuilder.java

/*
 * Copyright (C) 2003-2008 eXo Platform SAS.
 *
 * This program is free software; you can redistribute it and/or
 * modify it under the terms of the GNU Affero General Public License
 * as published by the Free Software Foundation; either version 3
 * of the License, or (at your option) any later version.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with this program; if not, see<http://www.gnu.org/licenses/>.
 */
package org.exoplatform.services.wcm.utils;

import java.util.Calendar;

import org.apache.commons.lang.StringUtils;
import org.exoplatform.commons.utils.ISO8601;



/**
 * Created by The eXo Platform SAS
 * Author : Hoa Pham
 * hoa.pham@exoplatform.com
 * Oct 7, 2008
 */

/*
 * This class is implementation of AbstractQueryBuilder for sql-based query.
 * This class help developer create sql query statement easier.
 * */
public class SQLQueryBuilder extends AbstractQueryBuilder {

  /*
   * (non-Javadoc)
   * @see
   * org.exoplatform.services.wcm.search.AbstractQueryBuilder#isNull(java.lang
   * .String, org.exoplatform.services.wcm.search.AbstractQueryBuilder.LOGICAL)
   */
  public void isNull(String propertyName, LOGICAL condition) {
    if(condition == LOGICAL.AND)
      propertiesClause.append(" AND").append(propertyName).append("IS NULL ");
    else if(condition == LOGICAL.OR)
      propertiesClause.append(" OR").append(propertyName).append("IS NULL ");
    else
      propertiesClause.append(propertyName).append("IS NULL ");
  }

  /*
   * (non-Javadoc)
   * @see
   * org.exoplatform.services.wcm.search.AbstractQueryBuilder#isNotNull(java
   * .lang.String,
   * org.exoplatform.services.wcm.search.AbstractQueryBuilder.LOGICAL)
   */
  public void isNotNull(String propertyName, LOGICAL condition) {
    if(condition == LOGICAL.AND)
      propertiesClause.append("AND ").append(propertyName).append(" IS NOT NULL ");
    else if(condition == LOGICAL.OR)
      propertiesClause.append("OR ").append(propertyName).append(" IS NOT NULL ");
    else
      propertiesClause.append(propertyName).append(" IS NOT NULL ");
  }

  /*
   * (non-Javadoc)
   * @see
   * org.exoplatform.services.wcm.search.AbstractQueryBuilder#lessThan(java.
   * lang.String, java.lang.String,
   * org.exoplatform.services.wcm.search.AbstractQueryBuilder.LOGICAL)
   */
  public void lessThan(String propertyName, String value, LOGICAL condition) {
    comparison(propertyName,value,condition,"<");
  }

  /*
   * (non-Javadoc)
   * @see
   * org.exoplatform.services.wcm.search.AbstractQueryBuilder#greaterThan(java
   * .lang.String, java.lang.String,
   * org.exoplatform.services.wcm.search.AbstractQueryBuilder.LOGICAL)
   */
  public void greaterThan(String propName, String value, LOGICAL condition) {
    comparison(propName,value,condition,">");
  }

  /*
   * (non-Javadoc)
   * @see
   * org.exoplatform.services.wcm.search.AbstractQueryBuilder#lessThanOrEqual
   * (java.lang.String, java.lang.String,
   * org.exoplatform.services.wcm.search.AbstractQueryBuilder.LOGICAL)
   */
  public void lessThanOrEqual(String propName, String value, LOGICAL condition) {
    comparison(propName,value,condition,"<=");
  }

  /*
   * (non-Javadoc)
   * @see
   * org.exoplatform.services.wcm.search.AbstractQueryBuilder#greaterOrEqual
   * (java.lang.String, java.lang.String,
   * org.exoplatform.services.wcm.search.AbstractQueryBuilder.LOGICAL)
   */
  public void greaterOrEqual(String propName,String value, LOGICAL condition) {
    comparison(propName,value,condition,">=");
  }

  /*
   * (non-Javadoc)
   * @see
   * org.exoplatform.services.wcm.search.AbstractQueryBuilder#equal(java.lang
   * .String, java.lang.String,
   * org.exoplatform.services.wcm.search.AbstractQueryBuilder.LOGICAL)
   */
  public void equal(String propName, String value , LOGICAL condition) {
    comparison(propName,value,condition,"=");
  }

  /*
   * (non-Javadoc)
   * @see
   * org.exoplatform.services.wcm.search.AbstractQueryBuilder#notEqual(java.
   * lang.String, java.lang.String,
   * org.exoplatform.services.wcm.search.AbstractQueryBuilder.LOGICAL)
   */
  public void notEqual(String propName, String value, LOGICAL condition) {
    if(condition == LOGICAL.AND)
      propertiesClause.append("AND ").append(propName).append(" <> '").append(value).append("' ");
    else if(condition == LOGICAL.OR)
      propertiesClause.append("OR ").append(propName).append(" <> '").append(value).append("' ");
    else
      propertiesClause.append(propName).append(" <> '").append(value).append("' ");
  }

  /**
   * Comparison.
   *
   * @param propName the prop name
   * @param value the value
   * @param condition the condition
   * @param symbol the symbol
   */
  private void comparison(String propName, String value, LOGICAL condition, String symbol) {
    if(condition == LOGICAL.AND)
      propertiesClause.append("AND ").append(propName).append(" ").append(symbol).append(" '").append(value).append("' ");
    else if(condition == LOGICAL.OR)
      propertiesClause.append("OR ").append(propName).append(" ").append(symbol).append(" '").append(value).append("' ");
    else
      propertiesClause.append(propName).append(" ").append(symbol).append(" '").append(value).append("' ");
  }

  /*
   * (non-Javadoc)
   * @see
   * org.exoplatform.services.wcm.search.AbstractQueryBuilder#like(java.lang
   * .String, java.lang.String,
   * org.exoplatform.services.wcm.search.AbstractQueryBuilder.LOGICAL)
   */
  public void like(String propName, String value, LOGICAL condition) {
    if(condition == LOGICAL.AND)
      propertiesClause.append("AND ").append(propName).append(" LIKE '").append(value).append("%' ");
    else if(condition == LOGICAL.OR)
      propertiesClause.append("OR ").append(propName).append(" LIKE '").append(value).append("%' ");
    else
      propertiesClause.append(propName).append(" LIKE '").append(value).append("%' ");
  }

  /*
   * (non-Javadoc)
   * @see
   * org.exoplatform.services.wcm.search.AbstractQueryBuilder#reference(java
   * .lang.String, java.lang.String,
   * org.exoplatform.services.wcm.search.AbstractQueryBuilder.LOGICAL)
   */
  public void reference(String propName, String value, LOGICAL condition) {
  }

  /*
   * (non-Javadoc)
   * @see
   * org.exoplatform.services.wcm.search.AbstractQueryBuilder#beforeDate(java
   * .lang.String, java.lang.String,
   * org.exoplatform.services.wcm.search.AbstractQueryBuilder.LOGICAL)
   */
  public void beforeDate(String propName, String comparedDate, LOGICAL condition) {
    Calendar calendar = ISO8601.parse(comparedDate);
    String time = calendar.getTime().toString();
    if(condition == LOGICAL.AND)
      propertiesClause.append("AND ").append(propName).append(" <= '").append(time).append("' ");
    else if(condition == LOGICAL.OR)
      propertiesClause.append("OR ").append(propName).append(" <= '").append(time).append("' ");
    else

      propertiesClause.append(propName).append(" <= '").append(time).append("' ");
  }

  /*
   * (non-Javadoc)
   * @see
   * org.exoplatform.services.wcm.search.AbstractQueryBuilder#afterDate(java
   * .lang.String, java.lang.String,
   * org.exoplatform.services.wcm.search.AbstractQueryBuilder.LOGICAL)
   */
  public void afterDate(String propName, String comparedDate, LOGICAL condition) {
    Calendar calendar = ISO8601.parse(comparedDate);
    String time = calendar.getTime().toString();
    if(condition == LOGICAL.AND)
      propertiesClause.append("AND ").append(propName).append(" >= '").append(time).append("' ");
    else if(condition == LOGICAL.OR)
      propertiesClause.append("OR ").append(propName).append(" >= '").append(time).append("' ");
    else
      propertiesClause.append(propName).append(" >= '").append(time).append("' ");
  }

  /*
   * (non-Javadoc)
   * @see
   * org.exoplatform.services.wcm.search.AbstractQueryBuilder#betweenDates(java
   * .lang.String, java.lang.String, java.lang.String,
   * org.exoplatform.services.wcm.search.AbstractQueryBuilder.LOGICAL)
   */
  public void betweenDates(String propName, String startDate, String endDate, LOGICAL condition) {
    String startTime = ISO8601.parse(startDate).getTime().toString();
    String endTime = ISO8601.parse(endDate).getTime().toString();
    if (condition == LOGICAL.AND)
      propertiesClause.append("AND ")
                      .append(propName)
                      .append(" between TIMESTAMP '")
                      .append(startTime)
                      .append("' and TIMESTAMP '")
                      .append(endTime)
                      .append("' ");
    else if (condition == LOGICAL.OR)
      propertiesClause.append("OR ")
                      .append(propName)
                      .append(" between TIMESTAMP '")
                      .append(startTime)
                      .append("' and  TIMESTAMP '")
                      .append(endTime)
                      .append("' ");
    else
      propertiesClause.append(propName)
                      .append(" between TIMESTAMP '")
                      .append(startTime)
                      .append("' and TIMESTAMP '")
                      .append(endTime)
                      .append("' ");
  }

  public void betweenDates(String propName, Calendar startDate, Calendar endDate, LOGICAL condition) {
    String startTime = ISO8601.format(startDate);
    String endTime = ISO8601.format(endDate);
    if (condition == LOGICAL.AND)
      propertiesClause.append("AND ")
                      .append(propName)
                      .append(" between TIMESTAMP '")
                      .append(startTime)
                      .append("' and TIMESTAMP '")
                      .append(endTime)
                      .append("' ");
    else if (condition == LOGICAL.OR)
      propertiesClause.append("OR ")
                      .append(propName)
                      .append(" between TIMESTAMP '")
                      .append(startTime)
                      .append("' and TIMESTAMP '")
                      .append(endTime)
                      .append("' ");
    else
      propertiesClause.append(propName)
                      .append(" between TIMESTAMP '")
                      .append(startTime)
                      .append("' and TIMESTAMP '")
                      .append(endTime)
                      .append("' ");
  }

  /*
   * (non-Javadoc)
   * @see
   * org.exoplatform.services.wcm.search.AbstractQueryBuilder#setQueryPath(java
   * .lang.String,
   * org.exoplatform.services.wcm.search.AbstractQueryBuilder.PATH_TYPE)
   */
  public void setQueryPath(String path, PATH_TYPE pathtype) {
    if(StringUtils.isEmpty(path)) return;
    if (PATH_TYPE.EXACT == pathtype) {
      if (path.indexOf("[%]") > 0)
        pathClause = new StringBuilder().append("jcr:path LIKE '").append(path).append("' ");
      else
        pathClause = new StringBuilder().append("jcr:path = '").append(path).append("' ");
    } else if (PATH_TYPE.CHILDNODES == pathtype) {
      pathClause = new StringBuilder().append("jcr:path LIKE '")
                                      .append(path)
                                      .append("/%'")
                                      .append("AND NOT jcr:path like '")
                                      .append(path)
                                      .append("/%/%' ");
    } else if (PATH_TYPE.DECENDANTS == pathtype) {
      pathClause = new StringBuilder().append("jcr:path LIKE '").append(path).append("/%' ");
    } else if (PATH_TYPE.DECENDANTS_OR_SELFT == pathtype) {
      pathClause = new StringBuilder().append("jcr:path LIKE '")
                                      .append(path)
                                      .append("'")
                                      .append("OR jcr:path LIKE '")
                                      .append(path)
                                      .append("/%' ");
    }
  }

  /*
   * (non-Javadoc)
   * @see
   * org.exoplatform.services.wcm.search.AbstractQueryBuilder#contains(java.
   * lang.String, java.lang.String,
   * org.exoplatform.services.wcm.search.AbstractQueryBuilder.LOGICAL)
   */
  public void contains(String scope, String term, LOGICAL condition) {
    if (scope == null)
      scope = ".";
    if (LOGICAL.AND == condition)
      containsClause.append("AND CONTAINS(").append(scope).append(",'").append(term).append("') ");
    else if (LOGICAL.OR == condition)
      containsClause.append("OR CONTAINS(").append(scope).append(",'").append(term).append("') ");
    else
      containsClause.append("CONTAINS(").append(scope).append(",'").append(term).append("') ");
  }

  /*
   * (non-Javadoc)
   * @see
   * org.exoplatform.services.wcm.search.AbstractQueryBuilder#notContains(java
   * .lang.String, java.lang.String,
   * org.exoplatform.services.wcm.search.AbstractQueryBuilder.LOGICAL)
   */
  public void notContains(String scope, String term, LOGICAL condition) {
    if (scope == null)
      scope = ".";
    if (LOGICAL.AND == condition)
      containsClause.append("AND NOT CONTAINS(")
                    .append(scope)
                    .append(",'")
                    .append(term)
                    .append("') ");
    else if (LOGICAL.OR == condition)
      containsClause.append("OR NOT CONTAINS(")
                    .append(scope)
                    .append(",'")
                    .append(term)
                    .append("') ");
    else
      containsClause.append("NOT CONTAINS(").append(scope).append(",'").append(term).append("') ");
  }

  /* (non-Javadoc)
   * @see org.exoplatform.services.wcm.search.AbstractQueryBuilder#fromNodeTypes(java.lang.String[])
   */
  public void fromNodeTypes(String[] nodetypes) {
    if(nodetypes == null) {
      fromClause = new StringBuilder("FROM nt:base");
      return;
    }
    fromClause = new StringBuilder("FROM ");
    for(int i = 0; i<nodetypes.length; i++) {
      fromClause.append(nodetypes[i]);
      if(i<nodetypes.length-1)
        fromClause.append(",");
    }
  }

  /* (non-Javadoc)
   * @see org.exoplatform.services.wcm.search.AbstractQueryBuilder#selectTypes(java.lang.String[])
   */
  public void selectTypes(String[] returnTypes) {
    if(returnTypes == null) {
      selectClause = new StringBuilder("SELECT * ");
      return;
    }
    selectClause = new StringBuilder("SELECT ");
    for(int i = 0; i<returnTypes.length; i++) {
      selectClause.append(returnTypes[i]);
      if(i<returnTypes.length-1)
        selectClause.append(",");
      selectClause.append(" ");
    }
  }

  /*
   * (non-Javadoc)
   * @see
   * org.exoplatform.services.wcm.search.AbstractQueryBuilder#orderBy(java.lang
   * .String, org.exoplatform.services.wcm.search.AbstractQueryBuilder.ORDERBY)
   */
  public void orderBy(String properyName, ORDERBY orderby) {
    if(orderByClause.length()>0)
      orderByClause = orderByClause.append(", ");
    if(ORDERBY.ASC == orderby)
      orderByClause.append(properyName).append(" ASC");
    else
      orderByClause.append(properyName).append(" DESC");
  }

  /* (non-Javadoc)
   * @see org.exoplatform.services.wcm.search.AbstractQueryBuilder#excerpt(boolean)
   */
  public void excerpt(boolean enable) {
    if(enable)
      excerptClause = new StringBuilder("excerpt(.)");
  }

  /* (non-Javadoc)
   * @see org.exoplatform.services.wcm.search.AbstractQueryBuilder#spellCheck(java.lang.String)
   */
  public void spellCheck(String value) {
  }

  /*
   * (non-Javadoc)
   * @see
   * org.exoplatform.services.wcm.search.AbstractQueryBuilder#openGroup(org.
   * exoplatform.services.wcm.search.AbstractQueryBuilder.LOGICAL)
   */
  public void openGroup(LOGICAL logical) {
    if(LOGICAL.AND == logical)
    propertiesClause = propertiesClause.append("AND( ");
    else if(LOGICAL.OR == logical)
      propertiesClause = propertiesClause.append("OR( ");
    else if(LOGICAL.AND_NOT == logical)
      propertiesClause = propertiesClause.append("AND NOT(");
    else if(LOGICAL.OR_NOT == logical)
      propertiesClause = propertiesClause.append("OR NOT(");
    else
      propertiesClause = propertiesClause.append("( ");
  }

  /* (non-Javadoc)
   * @see org.exoplatform.services.wcm.search.AbstractQueryBuilder#closeGroup()
   */
  public void closeGroup() {
    propertiesClause = propertiesClause.append(")");
  }

  /* (non-Javadoc)
   * @see org.exoplatform.services.wcm.search.AbstractQueryBuilder#createQueryStatement()
   */
  public String createQueryStatement() {
    StringBuffer statement = new StringBuffer();
    statement = statement.append(selectClause.toString())
                         .append(fromClause.toString())
                         .append(" WHERE ");
    if (containsClause.length() > 0) {
      statement = statement.append(containsClause.toString());
      if (pathClause.length() > 0) {
        statement = statement.append("AND ").append(pathClause.toString());
      }
    } else {
      if (pathClause.length() > 0) {
        statement = statement.append(pathClause.toString());
      }
    }
    if (propertiesClause.length() > 0) {
      String propertiesStr = propertiesClause.toString();
      if((propertiesStr.startsWith("AND") || propertiesStr.startsWith(" AND")) &&
          statement.toString().endsWith("WHERE ")) 
        propertiesStr = propertiesStr.substring(propertiesStr.lastIndexOf("AND") + 1, propertiesStr.length());
      statement = statement.append(propertiesStr);
    }
    if (orderByClause.length() > 0) {
      statement = statement.append("ORDER BY ").append(orderByClause.toString());
    }
    return statement.toString();
  }

  /*
   * (non-Javadoc)
   * @see
   * org.exoplatform.services.wcm.search.AbstractQueryBuilder#merge(org.exoplatform
   * .services.wcm.search.AbstractQueryBuilder)
   */
  public void merge(AbstractQueryBuilder other) {
  }

  public void queryByNodeName(String rootPath, String nodeName) {
    pathClause = new StringBuilder().append(" jcr:path LIKE '")
                                    .append(rootPath)
                                    .append("/%/")
                                    .append(nodeName)
                                    .append("' ")
                                    .append(" or jcr:path like '")
                                    .append(rootPath)
                                    .append("/")
                                    .append(nodeName)
                                    .append("' ");
  }
}