Application: gvSIG desktop

package org.gvsig.fmap.dal.feature.spi;

import java.text.MessageFormat;

import java.util.ArrayList;

import java.util.Collections;

import java.util.HashMap;

import java.util.HashSet;

import java.util.List;

import java.util.Map;

import java.util.Objects;

import java.util.Set;

import org.apache.commons.lang3.StringUtils;

import org.apache.commons.lang3.tuple.ImmutablePair;

import org.apache.commons.lang3.tuple.Pair;

import org.cresques.cts.IProjection;

import org.gvsig.expressionevaluator.ExpressionBuilder;

import org.gvsig.expressionevaluator.ExpressionBuilder.AbstractValue;

import org.gvsig.expressionevaluator.ExpressionBuilder.ClassVisitorFilter;

import static org.gvsig.expressionevaluator.ExpressionBuilder.PARAMETER_TYPE_CONSTANT;

import static org.gvsig.expressionevaluator.ExpressionBuilder.PARAMETER_TYPE_VARIABLE;

import org.gvsig.expressionevaluator.ExpressionBuilder.Parameter;

import static org.gvsig.expressionevaluator.ExpressionBuilder.VALUE_NULL;

import org.gvsig.expressionevaluator.ExpressionBuilder.Value;

import org.gvsig.expressionevaluator.ExpressionBuilder.Variable;

import org.gvsig.expressionevaluator.ExpressionBuilder.Visitable;

import org.gvsig.expressionevaluator.ExpressionBuilder.Visitor;

import org.gvsig.expressionevaluator.ExpressionBuilder.VisitorFilter;

import org.gvsig.expressionevaluator.Formatter;

import org.gvsig.expressionevaluator.GeometryExpressionBuilder;

import org.gvsig.expressionevaluator.GeometryExpressionBuilderHelper.GeometrySupportType;

import org.gvsig.expressionevaluator.GeometryExpressionEvaluatorLocator;

import org.gvsig.fmap.dal.DataManager;

import org.gvsig.fmap.dal.DataStoreParameters;

import org.gvsig.fmap.dal.DataTypes;

import org.gvsig.fmap.dal.SQLBuilder;

import org.gvsig.fmap.dal.SQLBuilder.AlterTableBuilder;

import org.gvsig.fmap.dal.SQLBuilder.CreateTableBuilder;

import org.gvsig.fmap.dal.SQLBuilder.DeleteBuilder;

import org.gvsig.fmap.dal.SQLBuilder.DropTableBuilder;

import org.gvsig.fmap.dal.SQLBuilder.FromBuilder;

import org.gvsig.fmap.dal.SQLBuilder.GrantBuilder;

import org.gvsig.fmap.dal.SQLBuilder.InsertBuilder;

import org.gvsig.fmap.dal.SQLBuilder.InsertColumnBuilder;

import org.gvsig.fmap.dal.SQLBuilder.OrderByBuilder;

import org.gvsig.fmap.dal.SQLBuilder.Privilege;

import org.gvsig.fmap.dal.SQLBuilder.SelectBuilder;

import org.gvsig.fmap.dal.SQLBuilder.SelectColumnBuilder;

import org.gvsig.fmap.dal.SQLBuilder.TableNameBuilder;

import org.gvsig.fmap.dal.SQLBuilder.UpdateBuilder;

import org.gvsig.fmap.dal.SQLBuilder.UpdateColumnBuilder;

import org.gvsig.fmap.dal.SQLBuilder.UpdateTableStatisticsBuilder;

import org.gvsig.fmap.dal.feature.FeatureAttributeDescriptor;

import org.gvsig.fmap.geom.Geometry;

import org.gvsig.fmap.geom.GeometryUtils;

import org.gvsig.fmap.geom.primitive.Envelope;

import org.gvsig.tools.dataTypes.DataType;

import org.gvsig.tools.dynobject.Tags;

import org.gvsig.tools.lang.CloneableUtils;

import org.gvsig.tools.util.PropertiesSupport;

import org.slf4j.Logger;

import org.slf4j.LoggerFactory;

@SuppressWarnings("UseSpecificCatch")

public class SQLBuilderBase implements SQLBuilder {

    protected static final Logger LOGGER = LoggerFactory.getLogger(SQLBuilderBase.class);

    protected SelectBuilder select;

    protected UpdateBuilder update;

    protected InsertBuilder insert;

    protected DeleteBuilder delete;

    protected AlterTableBuilder alter_table;

    protected CreateTableBuilder create_table;

    protected GrantBuilder grant;

    protected DropTableBuilder drop_table;

    protected UpdateTableStatisticsBuilder update_table_statistics;

    protected CreateIndexBuilder create_index;

    protected DropIndexBuilder drop_index;

    protected TableNameBuilder table_name;

    protected abstract class AbstractStatementPart extends AbstractValue {

    }

    protected abstract class AbstractStatement extends AbstractStatementPart {

        @Override

        public Value clone() throws CloneNotSupportedException {

            throw new CloneNotSupportedException();

        }

    }

    protected class ColumnDescriptorBase implements ColumnDescriptor {

        private String name;

        private int type;

        private int size;

        private int precision;

        private int scale;

        private boolean isPk;

        private boolean _allowNulls;

        private boolean _isAutomatic;

        private Object defaultValue;

        private int geom_type;

        private int geom_subtype;

        private Object geom_srsdbcode;

        private Envelope tablebbox;

        private boolean _isIndexed;

        private DataStoreParameters parameters = null;

        public ColumnDescriptorBase(String name, int type, Object defaultValue) {

            this.name = name;

            this.type = type;

            this.size = -1;

            this.precision = -1;

            this.scale = -1;

            this.isPk = false;

            this._allowNulls = true;

            this._isAutomatic = false;

            this.defaultValue = defaultValue;

            this.geom_type = Geometry.TYPES.GEOMETRY;

            this.geom_subtype = Geometry.SUBTYPES.GEOM2D;

            this.geom_srsdbcode = null;

            this.tablebbox = null;

            this._isIndexed = false;

        }

        public ColumnDescriptorBase(String name, int type, int size, int precision, int scale, boolean isPk, boolean isIndexed, boolean allowNulls, boolean isAutomatic, Object defaultValue) {

            this.name = name;

            this.type = type;

            this.size = size;

            this.precision = precision;

            this.scale = scale;

            this.isPk = isPk;

            this._allowNulls = allowNulls;

            this._isAutomatic = isAutomatic;

            this.defaultValue = defaultValue;

            this.geom_type = Geometry.TYPES.GEOMETRY;

            this.geom_subtype = Geometry.SUBTYPES.GEOM2D;

            this.geom_srsdbcode = null;

            this.tablebbox = null;

            this._isIndexed = isIndexed;

        }

        public ColumnDescriptorBase(String name, int geom_type, int geom_subtype, IProjection proj, boolean isIndexed, boolean allowNulls) {

            this.name = name;

            this.type = DataTypes.GEOMETRY;

            this.size = 0;

            this.precision = 0;

            this.scale = 0;

            this.isPk = false;

            this._allowNulls = allowNulls;

            this._isAutomatic = false;

            this.defaultValue = null;

            this.geom_type = geom_type;

            this.geom_subtype = geom_subtype;

            this.geom_srsdbcode = srs_id(proj);

            this.tablebbox = null;

            this._isIndexed = isIndexed;

        }

        public ColumnDescriptorBase(String name, int geom_type, int geom_subtype, Object srsdbcode, boolean isIndexed, boolean allowNulls) {

            this.name = name;

            this.type = DataTypes.GEOMETRY;

            this.size = 0;

            this.precision = 0;

            this.scale = 0;

            this.isPk = false;

            this._allowNulls = allowNulls;

            this._isAutomatic = false;

            this.defaultValue = null;

            this.geom_type = geom_type;

            this.geom_subtype = geom_subtype;

            this.geom_srsdbcode = srsdbcode;

            this.tablebbox = null;

            this._isIndexed = isIndexed;

        }

        private ColumnDescriptorBase(FeatureAttributeDescriptor fad) {

            this(fad.getName(), fad.getType(), fad.getDefaultValue());

            this.precision = fad.getPrecision();

            this.size = fad.getSize();

            this.scale = fad.getScale();

            this.isPk = fad.isPrimaryKey();

            this._allowNulls = fad.allowNull();

            this._isAutomatic = fad.isAutomatic();

            this._isIndexed = fad.isIndexed();

            if (fad.getType() == org.gvsig.fmap.geom.DataTypes.GEOMETRY) {

                this.geom_type = fad.getGeomType().getType();

                this.geom_subtype = fad.getGeomType().getSubType();

                this.geom_srsdbcode =  srs_id(fad.getSRS());

                this.tablebbox = null;

                Tags tags = fad.getTags();

                String s = tags.getString("tablebbox", null);

                if( StringUtils.isNotBlank(s) ) {

                    try {

                        Geometry g = GeometryUtils.createFrom(s);

                        if( g!=null ) {

                            this.tablebbox = g.getEnvelope();

                        }

                    } catch(Exception ex) {

                        LOGGER.warn("Can't parse tablebbox for column '"+s+"'.",ex);

                    }

                }

            }

        }

        @Override

        public String getName() {

            return this.name;

        }

        @Override

        public void setName(String name) {

            this.name = name;

        }

        @Override

        public int getType() {

            return this.type;

        }

        @Override

        public void setType(int type) {

            this.type = type;

        }

        @Override

        public int getPrecision() {

            return precision;

        }

        @Override

        public void setPrecision(int precision) {

            this.precision = precision;

        }

        @Override

        public int getScale() {

            return scale;

        }

        @Override

        public void setScale(int scale) {

            this.scale = scale;

        }

        @Override

        public int getSize() {

            return size;

        }

        @Override

        public void setSize(int size) {

            this.size = size;

        }

        @Override

        public boolean isPrimaryKey() {

            return isPk;

        }

        @Override

        public void setIsPrimaryKey(boolean isPk) {

            this.isPk = isPk;

        }

        @Override

        public boolean allowNulls() {

            return _allowNulls;

        }

        @Override

        public void setAllowNulls(boolean allowNulls) {

            this._allowNulls = allowNulls;

        }

        @Override

        public boolean isAutomatic() {

            return _isAutomatic;

        }

        @Override

        public boolean isIndexed() {

            return _isIndexed;

        }

        @Override

        public void setIsAutomatic(boolean isAutomatic) {

            this._isAutomatic = isAutomatic;

        }

        @Override

        public Object getDefaultValue() {

            return defaultValue;

        }

        @Override

        public void setDefaultValue(Object defaultValue) {

            this.defaultValue = defaultValue;

        }

        @Override

        public int getGeometryType() {

            return geom_type;

        }

        @Override

        public void setGeometryType(int geom_type) {

            this.geom_type = geom_type;

        }

        @Override

        public int getGeometrySubtype() {

            return geom_subtype;

        }

        @Override

        public void setGeometrySubtype(int geom_subtype) {

            this.geom_subtype = geom_subtype;

        }

        @Override

        public Object getGeometrySRSId() {

            return geom_srsdbcode;

        }

        @Override

        public void setGeometrySRSId(Object geom_srsid) {

            this.geom_srsdbcode = geom_srsid;

        }

        @Override

        public boolean isGeometry() {

            return this.type == DataTypes.GEOMETRY;

        }

        private void setStoreParameters(DataStoreParameters parameters) {

            this.parameters = parameters;

        }

        @Override

        public DataStoreParameters getStoreParameters() {

            return this.parameters;

        }

        public Envelope getTableBBox() {

            return this.tablebbox;

        }

        public void setTableBBox(Envelope bbox) {

            this.tablebbox = bbox;

        }

    }

    public class ColumnBase extends AbstractValue implements Column {

        private final String name;

        private TableNameBuilder table;

        public ColumnBase(TableNameBuilder table, String name) {

            this.name = name;

            this.table = table;

        }

        @Override

        public ColumnBase clone() throws CloneNotSupportedException {

            ColumnBase other = (ColumnBase) super.clone();

            other.table = (TableNameBuilder) org.gvsig.tools.lang.Cloneable.cloneQuietly(table);

            return other;

        }

        @Override

        public String name() {

            return this.name;

        }

        @Override

        public TableNameBuilder table() {

            return this.table;

        }

        @Override

        public TableNameBuilder table(TableNameBuilder table) {

            this.table = table;

            return this.table;

        }

        @Override

        public String toString() {

            return this.toString(formatter());

        }

        @Override

        public String toString(Formatter<Value> formatter) {

            if( formatter!=null && formatter.canApply(this) ) {

                return formatter.format(this);

            }

            if( this.table==null ) {

                return as_identifier(this.name);

            }

            return this.table.toString(formatter) + "." + as_identifier(this.name);

        }

        @Override

        public int compareTo(Variable o) {

            return this.name.compareTo(o.name());

        }

        @Override

        public boolean equals(Object obj) {

            if (!(obj instanceof Variable)) {

                return false;

            }

            return StringUtils.equals(this.toString(), ((Variable) obj).toString());

        }

        @Override

        public int hashCode() {

            int hash = 7;

            hash = 37 * hash + Objects.hashCode(this.toString());

            return hash;

        }

    }

    public class TableNameBuilderBase

            extends AbstractStatementPart

            implements TableNameBuilder {

        public String tableName;

        public String schemaName;

        private String databaseName;

        public TableNameBuilderBase() {

        }

        @Override

        public void accept(Visitor visitor, VisitorFilter filter) {

            if (filter==null || filter.accept(this)) {

                visitor.visit(this);

            }

        }

        @Override

        public TableNameBuilder database(String name) {

            this.databaseName = name;

            return this;

        }

        @Override

        public TableNameBuilder schema(String name) {

            if (support_schemas()) {

                this.schemaName = name;

            }

            return this;

        }

        @Override

        public TableNameBuilder name(String name) {

            this.tableName = name;

            return this;

        }

        @Override

        public String getDatabase() {

            return this.databaseName;

        }

        @Override

        public String getSchema() {

            return this.schemaName;

        }

        @Override

        public String getName() {

            return this.tableName;

        }

        @Override

        public boolean has_schema() {

            if (!support_schemas()) {

                return false;

            }

            return StringUtils.isNotBlank(this.schemaName);

        }

        @Override

        public boolean has_name() {

            return StringUtils.isNotBlank(this.tableName);

        }

        @Override

        public boolean has_database() {

            return StringUtils.isNotBlank(this.databaseName);

        }

        @Override

        public boolean isEmpty() {

            return !this.has_database() && !this.has_schema() && !this.has_name();

        }

        @Override

        public String toString() {

            return this.toString(formatter());

        }

        @Override

        public String toString(Formatter<Value> formatter) {

            if (formatter!=null && formatter.canApply(this)) {

                return formatter.format(this);

            }

            if (this.has_database()) {

                if (this.has_schema()) {

                    return as_identifier(this.databaseName) + "."

                            + as_identifier(this.schemaName) + "."

                            + as_identifier(this.tableName);

                }

//                return as_identifier(this.databaseName) + "."

//                        + as_identifier(this.tableName);

            } else {

                if (this.has_schema()) {

                    return as_identifier(this.schemaName) + "."

                            + as_identifier(this.tableName);

                }

            }

            return as_identifier(this.tableName);

        }

        @Override

        public boolean equals(Object obj) {

            if( obj==null || !(obj instanceof TableNameBuilder) ) {

                return false;

            }

            TableNameBuilder other = (TableNameBuilder) obj;

            if (this.has_database() != other.has_database()) {

                return false;

            }

            String thisSchema = null;

            String otherSchema = null;

            if(support_schemas()) {

                thisSchema = this.schemaName;

                if (StringUtils.isBlank(thisSchema)) {

                    thisSchema = default_schema();

                }

                otherSchema = other.getSchema();

                if (StringUtils.isBlank(otherSchema)) {

                    otherSchema = default_schema();

                }

            }

            if (this.has_database()) {

                    return StringUtils.equals(this.databaseName,other.getDatabase()) &&

                           StringUtils.equals(thisSchema, otherSchema) &&

                           StringUtils.equals(this.tableName,other.getName());

            } else {

                    return StringUtils.equals(thisSchema, otherSchema) &&

                           StringUtils.equals(this.tableName,other.getName());

            }

        }

        @Override

        public int hashCode() {

            int hash = 7;

            hash = 37 * hash + Objects.hashCode(this.toString());

            return hash;

        }

    }

    public class CountBuilderBase

            extends AbstractStatementPart

            implements CountBuilder {

        protected Value value;

        protected boolean distinct;

        protected boolean all;

        public CountBuilderBase() {

            this.value = null;

            this.distinct = false;

            this.all = false;

        }

        @Override

        public CountBuilderBase clone() throws CloneNotSupportedException {

            CountBuilderBase other = (CountBuilderBase) super.clone();

            other.value = (Value) org.gvsig.tools.lang.Cloneable.cloneQuietly(value);

            return other;

        }

        @Override

        public CountBuilder all() {

            this.all = true;

            return this;

        }

        @Override

        public CountBuilder column(Value value) {

            this.value = value;

            return this;

        }

        @Override

        public CountBuilder distinct() {

            this.distinct = true;

            return this;

        }

        @Override

        public String toString() {

            return this.toString(formatter());

        }

        @Override

        public String toString(Formatter formatter) {

            if (formatter!=null && formatter.canApply(this)) {

                return formatter.format(this);

            }

            if (this.all) {

                return "COUNT(*)";

            }

            if (this.distinct) {

                return MessageFormat.format(

                        "COUNT(DISTINCT {0})",

                        value.toString(formatter)

                );

            }

            return MessageFormat.format(

                    "COUNT({0})",

                    value.toString(formatter)

            );

        }

    }

    protected class JoinBase 

            extends AbstractStatementPart

            implements JoinBuilder 

        {

        protected String type;

        protected TableNameBuilder table;

        protected Value expression;

        public JoinBase(String type, TableNameBuilder table, Value expression) {

            this.type = type;

            this.table = table;

            this.expression = expression;

        }

        @Override

        public JoinBase clone() throws CloneNotSupportedException {

            JoinBase other = (JoinBase) super.clone();

            other.table = (TableNameBuilder) org.gvsig.tools.lang.Cloneable.cloneQuietly(table);

            other.expression = (Value) org.gvsig.tools.lang.Cloneable.cloneQuietly(expression);

            return other;

        }

        @Override

        public String toString() {

            return this.toString(formatter());

        }

        @Override

        public String toString(Formatter<Value> formatter) {

            if (formatter!=null && formatter.canApply(this)) {

                return formatter.format(this);

            }

            StringBuilder builder = new StringBuilder();

            // INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID

            builder.append(this.type.toUpperCase());

            builder.append(" JOIN ");

            builder.append(this.table.toString(formatter));

            builder.append(" ON ");

            builder.append(this.expression.toString(formatter));

            return builder.toString();

        }

        @Override

        public TableNameBuilder getTable() {

            return this.table;

        }

        @Override

        public String getType() {

            return this.type;

        }

        @Override

        public Value getCondition() {

            return this.expression;

        }

    }

    public class FromBuilderBase

            extends AbstractStatementPart

            implements FromBuilder {

        protected TableNameBuilder tableName;

        protected String subquery;

        protected String passthrough;

        protected List<JoinBuilder> joins;

        public FromBuilderBase() {

            this.tableName = null;

            this.subquery = null;

            this.passthrough = null;

            this.joins = null;

        }

        @Override

        public FromBuilderBase clone() throws CloneNotSupportedException {

            FromBuilderBase other = (FromBuilderBase) super.clone();

            other.tableName = (TableNameBuilder) org.gvsig.tools.lang.Cloneable.cloneQuietly(tableName);

            if (joins!=null) {

                for (int i = 0; i < joins.size(); i++) {

                    other.joins.set(i, (JoinBase) joins.get(i).clone());

                }

            }

            return other;

        }

        @Override

        public FromBuilder left_join(TableNameBuilder table, Value expression) {

            JoinBase join = createJoin("LEFT", table, expression);

            if( this.joins==null ) {

                this.joins = new ArrayList<>();

            }

            this.joins.add(join);

            return this;

        }

        @Override

        public TableNameBuilder table() {

            if (tableName == null) {

                this.tableName = createTableNameBuilder();

            }

            return this.tableName;

        }

        @Override

        public void accept(Visitor visitor, VisitorFilter filter) {

            boolean visitChildren = true;

            if (filter==null || filter.accept(this)) {

                visitor.visit(this);

            } else {

                visitChildren = !filter.skipChildren();

            }

            if(visitChildren){

                if (this.tableName != null) {

                    this.tableName.accept(visitor, filter);

                }

            }

        }

        @Override

        public FromBuilder custom(String passthrough) {

            this.passthrough = passthrough;

            return this;

        }

        @Override

        public FromBuilder subquery(String subquery) {

            this.subquery = subquery;

            return this;

        }

        @Override

        public String toString() {

            return this.toString(formatter());

        }

        @Override

        public String toString(Formatter<Value> formatter) {

            if (formatter!=null && formatter.canApply(this)) {

                return formatter.format(this);

            }

            if (!StringUtils.isEmpty(passthrough)) {

                return passthrough;

            }

            if (!StringUtils.isEmpty(subquery)) {

                return "( " + this.subquery + ") AS _subquery_alias_ ";

            }

            if( this.joins==null || this.joins.isEmpty() ) {

                return this.tableName.toString(formatter);

            }

            StringBuilder builder = new StringBuilder();

            builder.append(this.tableName.toString(formatter));

            for (JoinBuilder join : this.joins) {

                builder.append(" ");

                builder.append(join.toString(formatter));

            }

            return builder.toString();

        }

        @Override

        public List<JoinBuilder> getJoins() {

            return this.joins;

        }

    }

    public class SelectColumnBuilderBase

            extends AbstractStatementPart

            implements SelectColumnBuilder {

        protected Column name = null;

        protected String alias = null;

        protected Value value = null;

        protected boolean asGeometry = false;

        protected TableNameBuilder table;

        protected SQLBuilder sqlbuilder;

        public SelectColumnBuilderBase(SQLBuilder sqlbuilder) {

            this.sqlbuilder = sqlbuilder;

        }

        @Override

        public SelectColumnBuilderBase clone() throws CloneNotSupportedException {

            SelectColumnBuilderBase other = (SelectColumnBuilderBase) super.clone();

            other.value = (Value) org.gvsig.tools.lang.Cloneable.cloneQuietly(value);

            other.name = (Column) org.gvsig.tools.lang.Cloneable.cloneQuietly(name);

            other.table = (TableNameBuilder) org.gvsig.tools.lang.Cloneable.cloneQuietly(table);

            return other;

        }

        @Override

        public void accept(Visitor visitor, VisitorFilter filter) {

            boolean visitChildren = true;

            if (filter==null || filter.accept(this)) {

                visitor.visit(this);

            } else {

                visitChildren = !filter.skipChildren();

            }

            if(visitChildren){

                if (this.value != null) {

                    this.value.accept(visitor, filter);

                } else if (this.name != null) {

                    this.name.accept(visitor, filter);

                }

            }

        }

        @Override

        public void replace(Value target, Value replacement) {

            if (this.name!=null ) {

                if( this.name == target) {

                    if(replacement == null){

                        this.name = null;

                    } else if(replacement instanceof Column){

                        this.name = (Column) replacement;

                    } else if(replacement instanceof Variable){

                        this.name = new ColumnBase(this.table, ((Variable) replacement).name());

                    } else {

                        this.value = replacement;

                    }

                }

            }

            if( this.value!=null ) {

                if (this.value == target) {

                    this.value = replacement;

                } else {

                    this.value.replace(target, replacement);

                }

            }

        }

        @Override

        public SelectColumnBuilder name(String name) {

            return this.name(this.table, name);

        }

        @Override

        public SelectColumnBuilder name(TableNameBuilder table, String name) {

            String quote = quote_for_identifiers();

            if (name.startsWith(quote)) {

                // Remove quotes

                name = name.substring(1, name.length() - 1);

            }

            this.table = table;

            this.name = new ColumnBase(this.table, name);

            this.value = null;

            this.asGeometry = false;

            return this;

        }

        public SelectColumnBuilder table(TableNameBuilder table) {

            this.table = table;

            if(this.name != null){

                this.name.table(table);

            }

            return this;

        }

        @Override

        public SelectColumnBuilder all() {

            this.name = null;

            this.value = expression().custom("*");

            this.asGeometry = false;

            return this;

        }

        @Override

        public SelectColumnBuilder as_geometry() {

            this.asGeometry = true;

            return this;

        }

        @Override

        public SelectColumnBuilder value(Value value) {

            this.value = value;

            return this;

        }

        @Override

        public SelectColumnBuilder as(String alias) {

            this.alias = alias;

            return this;

        }

        @Override

        public String getName() {

            if (this.name==null) {

                return null;

            }

            return this.name.name();

        }

        @Override

        public String getAlias() {

            return this.alias;

        }

        @Override

        public Value getValue() {

            return this.value;

        }

        @Override

        public String toString() {

            return this.toString(formatter());

        }

        @Override

        public String toString(Formatter<Value> formatter) {

            if (formatter!=null && formatter.canApply(this)) {

                return formatter.format(this);

            }

            StringBuilder builder = new StringBuilder();

            if (this.asGeometry) {

                if(this.value == VALUE_NULL){

                    builder.append(this.value.toString(formatter));

                } else {

                    builder.append(expression().ST_AsBinary(this.name).toString(formatter));

                }

            } else {

                if (this.value == null) {

                    builder.append(this.name.toString(formatter));

                } else {

                    builder.append(this.value.toString(formatter));

                }

            }

            if (this.alias != null) {

                builder.append(" AS ");

                builder.append(as_identifier(this.alias));

            }

            return builder.toString();

        }

        @Override

        public boolean isGeometry() {

            return this.asGeometry;

        }

        @Override

        public TableNameBuilder getTable() {

            return this.table;

        }

        @Override

        public boolean isAggregateFunction() {

            if( this.value == null ) {

                return false;

            }

            if( !(this.value instanceof ExpressionBuilder.Function) ) {

                return false;

            }

            String funcname = ((ExpressionBuilder.Function)this.value).name();

            return this.sqlbuilder.isAggregateFunction(funcname);

        }

    }

    public class OrderByBuilderBase

            extends AbstractStatementPart

            implements OrderByBuilder {

        protected Value value;

        protected String custom;

        protected boolean ascending;

        protected int nullsMode;

        public OrderByBuilderBase() {

            this.ascending = true;

            this.nullsMode = MODE_NULLS_LAST;

        }

        @Override

        public OrderByBuilderBase clone() throws CloneNotSupportedException {

            OrderByBuilderBase other = (OrderByBuilderBase) super.clone();

            other.value = (Value) org.gvsig.tools.lang.Cloneable.cloneQuietly(value);

            return other;

        }

        @Override

        public void accept(Visitor visitor, VisitorFilter filter) {

            boolean visitChildren = true;

            if (filter==null || filter.accept(this)) {

                visitor.visit(this);

            } else {

                visitChildren = !filter.skipChildren();

            }

            if(visitChildren){

                if (this.value!=null) {

                    this.value.accept(visitor, filter);

                }

            }

        }

        @Override

        public OrderByBuilder column(String name) {

            this.value = expression().variable(name);

            return this;

        }

        @Override

        public boolean isColumn(String name) {

            if(this.value instanceof ExpressionBuilder.Variable){

                return StringUtils.equalsIgnoreCase(((ExpressionBuilder.Variable)this.value).name(), name);

            }

            return false;

        }

        @Override

        public boolean isColumn(Value value) {

            if(value instanceof ExpressionBuilder.Variable){

                return isColumn(((ExpressionBuilder.Variable)value).name());

            }

            return this.value == value;

        }

        @Override

        public OrderByBuilder value(Value expression) {

            this.value = expression;

            return this;

        }

        @Override

        public OrderByBuilder custom(String order) {

            this.custom = order;

            return this;

        }

        @Override

        public OrderByBuilder ascending() {

            this.ascending = true;

            return this;

        }

        @Override

        public OrderByBuilder ascending(boolean asc) {

            this.ascending = asc;

            return this;

        }

        @Override

        public OrderByBuilder descending() {

            this.ascending = false;

            return this;

        }

        @Override

        public OrderByBuilder nulls(int mode) {

            this.nullsMode = mode;

            return this;

        }

        @Override

        public int getNullsMode() {

            return this.nullsMode;

        }

        @Override

        public String toString() {

            return this.toString(formatter());

        }

        @Override

        public String toString(Formatter<Value> formatter) {

            if (formatter!=null && formatter.canApply(this)) {

                return formatter.format(this);

            }

            if (!StringUtils.isEmpty(this.custom)) {

                return this.custom;

            }

            String order_s = this.value.toString(formatter);

            if (this.ascending) {

                order_s += " ASC";

            } else {

                order_s += " DESC";

            }

            switch(this.nullsMode) {

                case MODE_NULLS_NOT_SPECIFIED:

                    break;

                case MODE_NULLS_FIRST:

                    order_s += " NULLS FIRST";

                    break;

                case MODE_NULLS_LAST:

                default:

                    order_s += " NULLS LAST";

                    break;

            }

            return order_s;

        }

        @Override

        public void replace(Value target, Value replacement) {

            super.replace(target, replacement);

            if(target == this.value){

                this.value = replacement;

                return;

            }

            if(this.value == null){

                return;

            }

            this.value.replace(target, replacement);

        }

    }

    public class SelectBuilderBase

            extends AbstractStatement

            implements SelectBuilder {

        protected FromBuilder from;

        protected GeometryExpressionBuilder where;

        protected long limit = -1;

        protected long offset = -1;

        protected List<SelectColumnBuilder> columns;

        protected List<OrderByBuilder> order_by;

        protected boolean distinct;

        protected List<Value> groupColumn;

        protected boolean check_order_and_offset = true;

        public SelectBuilderBase() {

            this.columns = new ArrayList<>();

            this.distinct = false;

        }

        @Override

        public List<Value> getGroups() {

            return this.groupColumn;

        }

        @Override

        public List<SelectColumnBuilder> getColumns() {

            return Collections.unmodifiableList(this.columns);

    }

        @Override

        public void remove_column(String columnName) {

            SelectColumnBuilder found = null;

            for (SelectColumnBuilder column : columns) {

                if(column.getAlias().equalsIgnoreCase(columnName)) {

                    found = column;

                    break;

                }

            }

            if(found!=null) {

                columns.remove(found);

            }

        }

        @Override

        public SelectBuilder group_by(Value... columns) {

            if( this.groupColumn==null ) {

                this.groupColumn = new ArrayList<>();

            }

            for (Value column : columns) {

                this.groupColumn.add(column);

            }

            return this;

        }

        @Override

        public void accept(Visitor visitor, VisitorFilter filter) {

            boolean visitChildren = true;

            if (filter==null || filter.accept(this)) {

                visitor.visit(this);

            } else {

                visitChildren = !filter.skipChildren();

            }

            if(visitChildren){

                for (SelectColumnBuilder column : columns) {

                    column.accept(visitor, filter);

                }

                if (this.has_from()) {

                    this.from.accept(visitor, filter);

                }

                if (this.has_where()) {

                    this.where.accept(visitor, filter);

                }

                if (this.has_order_by()) {

                    for (OrderByBuilder order : order_by) {

                        order.accept(visitor, filter);

                    }

                }

                if (this.has_group_by()) {

                    for (Value group : groupColumn) {

                        group.accept(visitor, filter);

                    }

                }

            }

        }

        @Override

        public void replace(Value target, Value replacement) {

            if( this.columns!=null ) {

                for (int i = 0; i < columns.size(); i++) {

                    SelectColumnBuilder column = columns.get(i);

                    if( column == target ) {

                        columns.set(i, (SelectColumnBuilder) replacement);

                    } else {

                        column.replace(target, replacement);

                    }

                }

            }

            if (this.has_from()) {

                if( this.from == target ) {

                    this.from = (FromBuilder) replacement;

                } else {

                    this.from.replace(target, replacement);

                }

            }

            if (this.has_where()) {

                if( this.where == target ) {

                    this.where = (GeometryExpressionBuilder) replacement;

                } else if( this.where.value() == target ) {

                    this.where.value(replacement);

                } else {

                    this.where.value().replace(target, replacement);

                }

            }

            if (this.has_order_by()) {

                for (int i = 0; i < order_by.size(); i++) {

                    OrderByBuilder order = order_by.get(i);

                    if( order == target ) {

                        order_by.set(i, (OrderByBuilder) replacement);

                    } else {

                        order.replace(target, replacement);

                    }

                }

            }

            if (this.has_group_by()) {

                for (int i = 0; i < groupColumn.size(); i++) {

                    Value group = groupColumn.get(i);

                    if( group == target ) {

                        groupColumn.set(i, replacement);

                    } else {

                        group.replace(target, replacement);

                    }

                }

            }

        }

        @Override

        public SelectBuilder distinct() {

            this.distinct = true;

            return this;

        }

        @Override