Application: gvSIG desktop

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

import java.text.MessageFormat;

import java.util.ArrayList;

import java.util.HashMap;

import java.util.HashSet;

import java.util.List;

import java.util.Map;

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.fmap.dal.DataTypes;

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

import org.gvsig.fmap.dal.feature.SQLBuilder_save.AlterTableBuilder;

import org.gvsig.fmap.dal.feature.SQLBuilder_save.ColumnBuilder;

import org.gvsig.fmap.dal.feature.SQLBuilder_save.CreateTableBuilder;

import org.gvsig.fmap.dal.feature.SQLBuilder_save.DeleteBuilder;

import org.gvsig.fmap.dal.feature.SQLBuilder_save.DropTableBuilder;

import org.gvsig.fmap.dal.feature.SQLBuilder_save.ExpBuilder;

import org.gvsig.fmap.dal.feature.SQLBuilder_save.FromBuilder;

import org.gvsig.fmap.dal.feature.SQLBuilder_save.GeometrySupportType;

import static org.gvsig.fmap.dal.feature.SQLBuilder_save.GeometrySupportType.EWKB;

import static org.gvsig.fmap.dal.feature.SQLBuilder_save.GeometrySupportType.WKB;

import static org.gvsig.fmap.dal.feature.SQLBuilder_save.GeometrySupportType.WKT;

import org.gvsig.fmap.dal.feature.SQLBuilder_save.GrantBuilder;

import org.gvsig.fmap.dal.feature.SQLBuilder_save.InsertBuilder;

import org.gvsig.fmap.dal.feature.SQLBuilder_save.OpBuilder;

import org.gvsig.fmap.dal.feature.SQLBuilder_save.Parameter;

import org.gvsig.fmap.dal.feature.SQLBuilder_save.Privilege;

import org.gvsig.fmap.dal.feature.SQLBuilder_save.SelectBuilder;

import org.gvsig.fmap.dal.feature.SQLBuilder_save.UpdateBuilder;

import org.gvsig.fmap.dal.feature.SQLBuilder_save.UpdateTableStatisticsBuilder;

import org.gvsig.fmap.geom.Geometry;

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

import org.gvsig.tools.dataTypes.DataType;

import org.slf4j.Logger;

import org.slf4j.LoggerFactory;

public class SQLBuilderBase_save implements SQLBuilder_save {

    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 List<Parameter> parameters;

    protected SQLBuilderConfig config;

    public class SQLBuilderConfig {

        public boolean allowAutomaticValues;

        public boolean has_spatial_functions;

        public String default_schema;

        public String quote_for_identifiers;

        public String quote_for_strings;

        public GeometrySupportType geometry_type_support;

        public String sql_true;

        public String ST_AsText;

        public String ST_AsBinary;

        public String ST_AsEWKB;

        public String ST_ExtentAggregate;

        public String ST_UnionAggregate;

        public String ST_Contains;

        public String ST_Crosses;

        public String ST_Disjoint;

        public String ST_IsClosed;

        public String ST_Overlaps;

        public String ST_Touches;

        public String ST_Within;

        public String ST_Intersects;

        public String ST_Envelope;

        public String ST_GeomFromText;

        public String ST_GeomFromWKB;

        public String ST_GeomFromEWKB;

        public String lcase;

        public String ucase;

        public String isNull;

        public String count;

        public String type_boolean;

        public String type_byte;

        public String type_bytearray;

        public String type_geometry;

        public String type_char;

        public String type_date;

        public String type_double;

        public String type_numeric_p;

        public String type_numeric_ps;

        public String type_bigdecimal;

        public String type_float;

        public String type_int;

        public String type_long;

        public String type_string;

        public String type_string_p;

        public String type_time;

        public String type_timestamp;

        public String type_version;

        public String type_URI;

        public String type_URL;

        public String type_FILE;

        public String type_FOLDER;;

        public String operator_AND;

        public String operator_OR;

        public String operator_EQ;

        public String operator_NE;

        public String operator_GT;

        public String operator_GE;

        public String operator_LT;

        public String operator_LE;

        public String operator_LIKE;

        public String operator_ILIKE;

        public String DELETE_FROM_table_WHERE_expresion;

        public String DELETE_FROM_table;

        public String INSERT_INTO_table_columns_VALUES_values;

        public String UPDATE_TABLE_STATISTICS_table;        

        public String DROP_TABLE_table;

        public String DELETE_GEOMETRY_COLUMN_FROM_TABLE_schema_table;

        public String DELETE_GEOMETRY_COLUMN_FROM_TABLE_table;

        public String UPDATE_table_SET_columnsAndValues_WHERE_expresion;

        public String UPDATE_table_SET_columnsAndValues;

        public SQLBuilderConfig() {

            has_spatial_functions = false;

            allowAutomaticValues = true;

            sql_true = "(1=1)";

            default_schema = "public";

            quote_for_identifiers = "\"";

            quote_for_strings = "'";

            geometry_type_support = GeometrySupportType.WKT;

            ST_AsText  = "ST_AsText({0})";

            ST_AsBinary = "ST_AsBinary({0})";

            ST_AsEWKB = "ST_AsWKB({0})";

            ST_ExtentAggregate = "ST_Extent({0})";

            ST_UnionAggregate = "ST_Union({0})";

            ST_Contains = "ST_Contains(({0}), ({1}))";            

            ST_Crosses =  "ST_Crosses(({0}), ({1}))";

            ST_Disjoint  =  "ST_Disjoint (({0}), ({1}))";

            ST_IsClosed =  "ST_IsClosed({0})";

            ST_Overlaps =  "ST_Overlaps(({0}), ({1}))";

            ST_Touches =  "ST_Touches(({0}), ({1}))";

            ST_Within = "ST_Within(({0}), ({1}))";            

            ST_Envelope = "ST_envelope({0})";

            ST_Intersects = "ST_intersects({0}, {1})";

            ST_GeomFromText = "ST_GeomFromText({0}, {1})";

            ST_GeomFromWKB = "ST_GeomFromWKB({0}, {1})";

            ST_GeomFromEWKB = "ST_GeomFromEWKB({0}, {1})";

            lcase = "LCASE({0})";

            ucase = "UCASE({0})";

            count = "count({0})";

            isNull = "{0} is null";

            /*

             * https://www.postgresql.org/docs/9.1/static/datatype.html

             * http://www.w3schools.com/sql/sql_datatypes.asp

             */

            type_boolean = "BOOLEAN";

            type_byte = "TINYINT";

            type_bytearray = "BYTEA";

            type_geometry = "TEXT";

            type_char = "CHARACTER(1)";

            type_date = "DATE";

            type_double = "DOUBLE PRECISION";

            type_numeric_p = "NUMERIC({0})";

            type_numeric_ps = "NUMERIC({0},{1})";

            type_bigdecimal = "NUMERIC({0},{1})";

            type_float = "REAL";

            type_int = "INT";

            type_long = "BIGINT";

            type_string = "TEXT";

            type_string_p = "VARCHAR({0})";

            type_time = "TIME";

            type_timestamp = "TIMESTAMP";

            type_version = "VARCHAR(30)";

            type_URI = "TEXT";

            type_URL = "TEXT";

            type_FILE = "TEXT";

            type_FOLDER = "TEXT";

            operator_AND = "{0} AND {1}";

            operator_OR = "{0} OR {1}";

            operator_EQ = "{0} = {1}";

            operator_NE = "{0} <> {1}";

            operator_GT = "{0} > {1}";

            operator_GE = "{0} >= {1}";

            operator_LT = "{0} < {1}";

            operator_LE = "{0} <= {1}";

            operator_LIKE = "{0} LIKE {1}";

            operator_ILIKE = "{0} ILIKE {1}";

            DELETE_FROM_table_WHERE_expresion = "DELETE FROM {0} WHERE {1}";

            DELETE_FROM_table = "DELETE FROM {0}";

            INSERT_INTO_table_columns_VALUES_values = "INSERT INTO {0} ( {1} ) VALUES ( {2} )";

            UPDATE_TABLE_STATISTICS_table = "VACUUM ANALYZE {0}";

            DROP_TABLE_table = "DROP TABLE {0}";

            DELETE_GEOMETRY_COLUMN_FROM_TABLE_schema_table = "DELETE FROM GEOMETRY_COLUMNS WHERE f_table_schema = {0} AND f_table_name = {1}";

            DELETE_GEOMETRY_COLUMN_FROM_TABLE_table = "DELETE FROM GEOMETRY_COLUMNS WHERE f_table_name = {0}";

            UPDATE_table_SET_columnsAndValues_WHERE_expresion = "UPDATE {0} SET {1} WHERE {2}";

            UPDATE_table_SET_columnsAndValues = "UPDATE {0} SET {1}";

        }

    }

    public class ParameterBase implements Parameter {

        protected int type;

        protected String name;

        protected IProjection crs;

        protected Object value;

        public ParameterBase(int type, String name, IProjection crs) {

            this.type = type;

            this.name = name;

            this.crs = crs;

            this.value = null;

        }

        public ParameterBase(Object value) {

            this.type = DataTypes.UNKNOWN;

            this.name = null;

            this.crs = null;

            this.value = value;

        }

        @Override

        public int getType() {

            return this.type;

        }

        @Override

        public String getName() {

            return this.name;

        }

        @Override

        public IProjection getCRS() {

            return this.crs;

        }

        @Override

        public boolean isConstant() {

            return this.name == null;

        }

        @Override

        public Object getValue() {

            return this.value;

        }

    }

    public class ParameterValue {

    }

    protected class ColumnInfo {

        public String name;

        public int type;

        public int type_p;

        public int type_s;

        public boolean isPk;

        public boolean allowNulls;

        public boolean isAutomatic;

        public Object defaultValue;

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

            this.name = name;

            this.type = type;

            this.type_p = -1;

            this.type_s = -1;

            this.isPk = false;

            this.allowNulls = true;

            this.isAutomatic = false;

            this.defaultValue = defaultValue;

        }

        public ColumnInfo(String name, int type, int type_p, int type_s, boolean isPk, boolean allowNulls, boolean isAutomatic, Object defaultValue) {

            this.name = name;

            this.type = type;

            this.type_p = type_p;

            this.type_s = type_s;

            this.isPk = isPk;

            this.allowNulls = allowNulls;

            this.isAutomatic = isAutomatic;

            this.defaultValue = defaultValue;

        }

    }

    public class OpBuilderBase implements OpBuilder {

        private String operador;

        private ExpBuilder op1;

        private ExpBuilder op2;

        public OpBuilderBase(String operador) {

            this.operador = operador;

        }

        public OpBuilderBase(String operador, String op1, String op2) {

            this.operador = operador;

            this.setop1(op1);

            this.setop2(op2);

        }

        @Override

        public OpBuilder setop1(String op) {

            this.op1 = createExpBuilder();

            this.op1.set(op);

            return this;

        }

        @Override

        public OpBuilder setop2(String op) {

            this.op2 = createExpBuilder();

            this.op2.set(op);

            return this;

        }

        protected OpBuilder add(String operator, ExpBuilder value) {

            if (this.op2 == null) {

                this.op2 = value;

                this.operador = operator;

                return this;

            }

            OpBuilderBase op = (OpBuilderBase) createOpBuilder(operator);

            op.op1 = this.op2;

            op.op2 = value;

            this.op2 = createExpresionBuilder(op);

            return this;

        }

        @Override

        public String toString() {

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

                return this.op1.toString();

            }

            return MessageFormat.format(this.operador, this.op1.toString(), this.op2.toString());

        }

    }

    public class ExpBuilderBase implements ExpBuilder {

        private String value;

        private OpBuilderBase op;

        public ExpBuilderBase() {

        }

        public ExpBuilderBase(String value) {

            this.value = value;

        }

        public ExpBuilderBase(OpBuilder op) {

            this.op = (OpBuilderBase) op;

        }

        @Override

        public boolean isEmpty() {

            return ( this.value == null && this.op == null );

        }

        @Override

        public ExpBuilder set(String value) {

            this.value = value;

            return this;

        }

        @Override

        public ExpBuilder set(OpBuilder op) {

            this.op = (OpBuilderBase) op;

            return this;

        }

        @Override

        public String toString() {

            if (this.value != null) {

                return this.value;

            }

            return this.op.toString();

        }

        protected ExpBuilder addOperator(String operator, String value) {

            if (this.op == null) {

                if (this.value == null) {

                    this.value = value;

                } else {

                    OpBuilderBase op = (OpBuilderBase) createOpBuilder(operator);

                    op.setop1(this.value);

                    op.setop2(value);

                    this.value = null;

                    this.op = op;

                }

            } else {

                this.op.add(operator, createExpresionBuilder(value));

            }

            return this;

        }

        @Override

        public ExpBuilder and(String value) {

            this.addOperator(config.operator_AND, value);

            return this;

        }

        @Override

        public ExpBuilder and(OpBuilder op) {

            this.addOperator(config.operator_AND, op.toString());

            return this;

        }

        @Override

        public ExpBuilder or(String value) {

            this.addOperator(config.operator_OR, value);

            return this;

        }

        @Override

        public ExpBuilder or(OpBuilder op) {

            this.addOperator(config.operator_OR, op.toString());

            return this;

        }

    }

    public class TableId {

        public String tableName;

        public String schemaName;

        private String dbName;

        public TableId(String dbName, String schemaName, String tableName) {

            this.dbName = dbName;

            this.tableName = tableName;

            this.schemaName = schemaName;

        }

        public TableId(String schemaName, String tableName) {

            this(null, schemaName, tableName);

        }

        public TableId(String tableName) {

            this(null, null, tableName);

        }

        public String getTableName() {

            return this.tableName;

        }

        public String getSchemaName() {

            return this.schemaName;

        }

        public boolean hasSchemaName() {

            return !StringUtils.isEmpty(this.schemaName);

        }

        public boolean hasDatabaseName() {

            return !StringUtils.isEmpty(this.dbName);

        }

        @Override

        public String toString() {

            if( this.hasDatabaseName() ) {

                if( this.hasSchemaName() ) {

                    return identifier(this.dbName) + "." + 

                           identifier(this.schemaName) + "." + 

                           identifier(this.tableName);

                }

            } else {

                if( this.hasSchemaName() ) {

                    return identifier(this.schemaName) + "." + 

                           identifier(this.tableName);

                }                

            }

            return identifier(this.tableName);

        }

    }

    public class FromBuilderBase implements FromBuilder {

        protected TableId tableName= null;

        private String subquery = null;

        private String passthrough = null;

        @Override

        public FromBuilder table(String dbName, String schemaName, String tableName) {

            this.tableName = new TableId(dbName, schemaName, tableName);

            return this;

        }

        @Override

        public FromBuilder table(String tableName) {

            this.tableName = new TableId(tableName);

            return this;

        }

        @Override

        public FromBuilder passThrough(String passthrough) {

            this.passthrough = passthrough;

            return this;

        }

        @Override

        public FromBuilder subquery(String subquery) {

            this.subquery = subquery;

            return this;

        }

        @Override

        public String toString() {

            if( ! StringUtils.isEmpty(passthrough) ) {

                return passthrough;

            }

            if( ! StringUtils.isEmpty(subquery) ) {

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

            }

            return this.tableName.toString();

        }

    }

    public class ColumnBuilderBase implements ColumnBuilder {

        private String name = null;

        private String expression = null;

        private String alias = null;

        @Override

        public ColumnBuilder name(String name) {

            String quote = getQuoteForIdentifiers();

            if (name.startsWith(quote)) {

                // Remove quotes

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

            }

            this.name = name;

            this.expression = null;

            return this;

        }

        @Override

        public ColumnBuilder expression(String value) {

            this.expression = value;

            this.name = null;

            return this;

        }

        @Override

        public ColumnBuilder geometry(String name) {

            if( config.has_spatial_functions ) {

                switch( geometry_support_type() ) {

                    case WKB:

                        this.expression = ST_AsBinary(name);

                        break;

                    case EWKB:

                        this.expression = ST_AsEWKB(name);

                        break;

                    case WKT:

                    default:

                        this.expression = ST_AsText(name);

                        break;

                }

                this.name = null;

            } else {

                this.name = name;

                this.expression = null;                

            }

            return this;

        }

        @Override

        public ColumnBuilder as(String alias) {

            this.alias = alias;

            return this;

        }

        @Override

        public String getName() {

            return this.name;

        }

        @Override

        public String getAlias() {

            return this.alias;

        }

        @Override

        public String getExpression() {

            return this.alias;

        }

        @Override

        public String toString() {

            StringBuilder builder = new StringBuilder();

            if( this.name != null ) {

                builder.append(identifier(this.name));

            } else {

                builder.append(this.expression);

            }

            if( this.alias != null ) {

                builder.append(" AS ");

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

            }

            return builder.toString();

        }

    }

    public class SelectBuilderBase implements SelectBuilder {

        protected FromBuilder from;

        protected ExpBuilder where;

        protected long limit = -1;

        protected long offset = -1;

        protected List<ColumnBuilder> columns;

        protected List<Pair<String,Boolean>> order_by;

        public SelectBuilderBase() {

            this.columns = new ArrayList<>();

        }

        @Override

        public ColumnBuilder column() {

            ColumnBuilder builder = createColumnBuilder();

            this.columns.add(builder);

            return builder;

        }

        @Override

        public boolean has_column(String name) {

            for (ColumnBuilder column : columns) {

                if( name.equals(column.getName()) ) {

                    return true;

                }

            }

            return false;

        }

        @Override

        public FromBuilder from() {

            if (this.from == null) {

                this.from = createFromBuilder();

            }

            return this.from;

        }

        @Override

        public FromBuilder from(String s) {

            return this.from().table(s);

        }

        @Override

        public ExpBuilder where() {

            if (this.where == null) {

                this.where = createExpBuilder();

            }

            return this.where;

        }

        @Override

        public ExpBuilder where(String s) {

            this.where = createExpBuilder();

            this.where.set(s);

            return this.where;

        }

        @Override

        public ExpBuilder where(OpBuilder s) {

            this.where = createExpBuilder();

            this.where.set(s);

            return this.where;

        }

        @Override

        public SelectBuilder limit(long limit) {

            this.limit = limit;

            return this;

        }

        @Override

        public SelectBuilder offset(long offset) {

            this.offset = offset;

            return this;

        }

        @Override

        public SelectBuilder add_order_by(String order) {

            this.order_by.add(new ImmutablePair(order,null));

            return this;

        }

        @Override

        public SelectBuilder add_order_by(String column, boolean ascending) {

            this.order_by.add(new ImmutablePair(column,ascending));

            return this;

        }

        @Override

        public boolean has_order_by() {

            if( this.order_by == null ) {

                return false;

            }

            return !this.order_by.isEmpty();

        }

        @Override

        public String toString() {

            StringBuilder builder = new StringBuilder();

            builder.append("SELECT ");

            boolean first = true;

            for (ColumnBuilder column : columns) {

                if (first) {

                    first = false;

                } else {

                    builder.append(", ");

                }

                builder.append(column.toString());

            }

            if (this.from != null) {

                builder.append(" FROM ");

                builder.append(this.from.toString());

            }

            if (this.where != null) {

                builder.append(" WHERE ");

                builder.append(this.where.toString());

            }

            if( this.has_order_by() ) {

                builder.append(" ORDER BY ");

                first = true;

                for (Pair<String,Boolean> item : this.order_by) {

                    if (first) {

                        first = false;

                    } else {

                        builder.append(", ");

                    }

                    builder.append(item.getLeft());                    

                    if( item.getRight()!=null ) {

                        // Si es null no a?adimos nada ya que puede habernos

                        // llegado ya un valor con el ASC o DESC incluido.

                        if( item.getRight() ) {

                            builder.append(" ASC");

                        } else {

                            builder.append(" DESC");

                        }

                    }

                }   

            }

            if (this.limit > 0) {

                builder.append(" LIMIT ");

                builder.append(this.limit);

            }

            if (this.offset > 0) {

                builder.append(" OFFSET ");

                builder.append(this.offset);

            }

            return builder.toString();

        }

    }

    public class DropTableBuilderBase implements DropTableBuilder {

        protected TableId table;

        @Override

        public DropTableBuilderBase table(String dbName, String schemaName, String tableName) {

            this.table = new TableId(dbName, schemaName, tableName);

            return this;

        }

        @Override

        public DropTableBuilderBase table(String tableName) {

            this.table = new TableId(tableName);

            return this;

        }

        @Override

        public String toString() {

            StringBuilder builder = new StringBuilder();

            boolean first = true;

            for (String sql : toStrings()) {

                if( StringUtils.isEmpty(sql) ) {

                    continue;

                }

                if (first) {

                    first = false;

                } else {

                    builder.append("; ");

                }

                builder.append(sql);

            }

            return builder.toString();

        }

        @Override

        public List<String> toStrings() {

            List<String> sqls = new ArrayList<>();

            sqls.add(

                    MessageFormat.format(

                            config.DROP_TABLE_table, 

                            this.table.toString()

                    )

            );

            String sql;

            if (this.table.hasSchemaName()) {

                sql = MessageFormat.format(

                        config.DELETE_GEOMETRY_COLUMN_FROM_TABLE_schema_table, 

                        identifier(this.table.getSchemaName()),

                        identifier(this.table.getTableName())

                );

            } else {

                sql = MessageFormat.format(

                        config.DELETE_GEOMETRY_COLUMN_FROM_TABLE_table, 

                        identifier(this.table.getTableName())

                );

            }

            if( !StringUtils.isEmpty(sql) ) {

                sqls.add(sql);

            }

            return sqls;

        }

    }

    public class GrantBuilderBase implements GrantBuilder {

        protected TableId table;

        protected Map<String, Set<Privilege>> privilegesByRole;

        @Override

        public GrantBuilderBase table(String dbName, String schemaName, String tableName) {

            this.table = new TableId(dbName, schemaName, tableName);

            return this;

        }

        @Override

        public GrantBuilderBase table(String tableName) {

            this.table = new TableId(tableName);

            return this;

        }

        @Override

        public GrantBuilder add(String roleid, Privilege privilege) {

            if (this.privilegesByRole.containsKey(roleid)) {

                this.privilegesByRole.get(roleid).add(privilege);

            } else {

                this.privilegesByRole.put(roleid, new HashSet<Privilege>());

                this.privilegesByRole.get(roleid).add(privilege);

            }

            return this;

        }

        @Override

        public GrantBuilder select(String roleid) {

            this.add(roleid, Privilege.SELECT);

            return this;

        }

        @Override

        public GrantBuilder insert(String roleid) {

            this.add(roleid, Privilege.INSERT);

            return this;

        }

        @Override

        public GrantBuilder delete(String roleid) {

            this.add(roleid, Privilege.DELETE);

            return this;

        }

        @Override

        public GrantBuilder truncate(String roleid) {

            this.add(roleid, Privilege.TRUNCATE);

            return this;

        }

        @Override

        public GrantBuilder reference(String roleid) {

            this.add(roleid, Privilege.REFERENCE);

            return this;

        }

        @Override

        public GrantBuilder trigger(String roleid) {

            this.add(roleid, Privilege.TRIGGER);

            return this;

        }

        @Override

        public GrantBuilder all(String roleid) {

            this.add(roleid, Privilege.ALL);

            return this;

        }

        @Override

        public GrantBuilder select() {

            this.add("PUBLIC", Privilege.SELECT);

            return this;

        }

        @Override

        public GrantBuilder insert() {

            this.add("PUBLIC", Privilege.INSERT);

            return this;

        }

        @Override

        public GrantBuilder delete() {

            this.add("PUBLIC", Privilege.DELETE);

            return this;

        }

        @Override

        public GrantBuilder truncate() {

            this.add("PUBLIC", Privilege.TRUNCATE);

            return this;

        }

        @Override

        public GrantBuilder reference() {

            this.add("PUBLIC", Privilege.REFERENCE);

            return this;

        }

        @Override

        public GrantBuilder trigger() {

            this.add("PUBLIC", Privilege.TRIGGER);

            return this;

        }

        @Override

        public GrantBuilder all() {

            this.add("PUBLIC", Privilege.ALL);

            return this;

        }

        @Override

        public String toString() {

            StringBuilder builder = new StringBuilder();

            boolean first = true;

            for (String sql : toStrings()) {

                if( StringUtils.isEmpty(sql) ) {

                    continue;

                }

                if (first) {

                    first = false;

                } else {

                    builder.append("; ");

                }

                builder.append(sql);

            }

            return builder.toString();

        }

        @Override

        public List<String> toStrings() {

            List<String> sqls = new ArrayList<>();

            if( this.privilegesByRole == null ) {

                return sqls;

            }

            Map<Privilege, String> privileges2SQL = new HashMap<>();

            privileges2SQL.put(Privilege.ALL, "ALL");

            privileges2SQL.put(Privilege.SELECT, "SELECT");

            privileges2SQL.put(Privilege.INSERT, "INSERT");

            privileges2SQL.put(Privilege.DELETE, "DELETE");

            privileges2SQL.put(Privilege.REFERENCE, "REFERENCE");

            privileges2SQL.put(Privilege.TRIGGER, "TRIGGER");

            privileges2SQL.put(Privilege.TRUNCATE, "TRUNCATE");

            privileges2SQL.put(Privilege.UPDATE, "UPDATE");

            for (String role : this.privilegesByRole.keySet()) {

                StringBuilder builder = new StringBuilder();

                builder.append("GRANT ");

                boolean first = true;

                for (Privilege privilege : this.privilegesByRole.get(role)) {

                    if (first) {

                        first = false;

                    } else {

                        builder.append(", ");

                    }

                    builder.append(privileges2SQL.get(privilege));

                }

                builder.append(" ON ");

                builder.append(this.table);

                builder.append(" TO ");

                builder.append(role);

                sqls.add(builder.toString());

            }

            return sqls;

        }

    }

    public class UpdateBuilderBase implements UpdateBuilder {

        protected ExpBuilder where;

        protected List<Pair<String,String>> columns;

        protected TableId table;

        public UpdateBuilderBase() {

            this.columns = new ArrayList<>();

        }

        @Override

        public ExpBuilder where() {

            if (this.where == null) {

                this.where = createExpBuilder();

            }

            return this.where;

        }

        @Override

        public UpdateBuilder table(String dbName, String schemaName, String tableName) {

            this.table = new TableId(dbName, schemaName, tableName);

            return this;

        }

        @Override

        public UpdateBuilder table(String tableName) {

            this.table = new TableId(tableName);

            return this;

        }

        @Override

        public UpdateBuilder set(String columnName, String value) {

            Pair<String,String> pair = new ImmutablePair<>(columnName, value);

            this.columns.add(pair);

            return this;

        }

        @Override

        public UpdateBuilder setGeometry(String columnName, String value, IProjection crs) {

            String g;

            switch( geometry_support_type() ) {

                case WKB:

                    g = ST_GeomFromWKB(value, crs(crs));

                    break;

                case EWKB:

                    g = ST_GeomFromEWKB(value, crs(crs));

                    break;

                case WKT:

                default:

                    g = ST_GeomFromText(value, crs(crs));

                    break;

            }

            this.set(identifier(columnName),g);

            return this;

        }

        @Override

        public boolean hasWhere() {

            return this.where != null;

        }

        @Override

        public String toString() {

            /*

             * UPDATE [ ONLY ] table [ [ AS ] alias ] SET { column = { expression |

             * DEFAULT } | ( column [, ...] ) = ( { expression | DEFAULT } [, ...] )

             * } [, ...] [ FROM fromlist ] [ WHERE condition ] [ RETURNING * |

             * output_expression [ AS output_name ] [, ...] ]

             */

            StringBuilder columnsAndValues = new StringBuilder();

            boolean first = true;

            for (Pair<String,String> column : columns) {

                if (first) {

                    first = false;

                } else {

                    columnsAndValues.append(", ");

                }

                columnsAndValues.append(identifier(column.getLeft()));

                columnsAndValues.append(" = ");

                columnsAndValues.append(column.getRight());

            }

            String sql;

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

                sql = MessageFormat.format(

                        config.UPDATE_table_SET_columnsAndValues,

                        this.table.toString(),

                        columnsAndValues.toString()

                );

            } else {

                sql = MessageFormat.format(

                        config.UPDATE_table_SET_columnsAndValues_WHERE_expresion,

                        this.table.toString(),

                        columnsAndValues.toString(),

                        this.where.toString()

                );

            }

            return sql;

        }

    }

    public class DeleteBuilderBase implements DeleteBuilder {

        protected ExpBuilder where;

        protected TableId table;

        public DeleteBuilderBase() {

        }

        @Override

        public ExpBuilder where() {

            if (this.where == null) {

                this.where = createExpBuilder();

            }

            return this.where;

        }

        @Override

        public DeleteBuilder table(String dbName, String schemaName, String tableName) {

            this.table = new TableId(dbName, schemaName, tableName);

            return this;

        }

        @Override

        public DeleteBuilder table(String tableName) {

            this.table = new TableId(tableName);

            return this;

        }

        @Override

        public boolean hasWhere() {

            return this.where != null;

        }

        @Override

        public String toString() {

            /*

             * DELETE FROM table_name

             * WHERE some_column=some_value; 

             */

            String sql;

            if( this.hasWhere() ) {

                sql = MessageFormat.format(

                        config.DELETE_FROM_table_WHERE_expresion,

                        this.table.toString(),

                        this.where.toString()

                );

            } else {

                sql = MessageFormat.format(

                        config.DELETE_FROM_table,

                        this.table.toString()

                );

            }

            return sql;

        }

    }

    public class AlterTableBuilderBase implements AlterTableBuilder {

        protected TableId table;

        protected List<String> drops;

        protected List<ColumnInfo> adds;

        protected List<ColumnInfo> alters;

        protected List<Pair> renames;

        public AlterTableBuilderBase() {

            this.drops = new ArrayList<>();

            this.adds = new ArrayList<>();

            this.alters = new ArrayList<>();

            this.renames = new ArrayList<>();

        }

        @Override

        public AlterTableBuilder table(String dbName, String schemaName, String tableName) {

            this.table = new TableId(dbName, schemaName, tableName);

            return this;

        }

        @Override

        public AlterTableBuilder table(String tableName) {

            this.table = new TableId(tableName);

            return this;

        }

        @Override

        public AlterTableBuilder drop_column(String columnName) {

            this.drops.add(columnName);

            return this;

        }

        @Override

        public AlterTableBuilder add_column(String columnName, int type, int type_p, int type_s, boolean isPk, boolean allowNulls, boolean isAutomatic, Object defaultValue) {

            if (isPk || isAutomatic) {

                allowNulls = false;

            }

            this.adds.add(new ColumnInfo(columnName, type, type_p, type_s, isPk, allowNulls, isAutomatic, defaultValue));

            return this;

        }

        @Override

        public AlterTableBuilder alter_column(String columnName, int type, int type_p, int type_s, boolean isPk, boolean allowNulls, boolean isAutomatic, Object defaultValue) {

            if (isPk || isAutomatic) {

                allowNulls = false;

            }

            this.alters.add(new ColumnInfo(columnName, type, type_p, type_s, isPk, allowNulls, isAutomatic, defaultValue));

            return this;

        }

        @Override

        public AlterTableBuilder rename_column(String source, String target) {

            this.renames.add(new ImmutablePair(source, target));

            return this;

        }

        @Override

        public String toString() {

            StringBuilder builder = new StringBuilder();

            boolean first = true;

            for (String sql : toStrings()) {

                if( StringUtils.isEmpty(sql) ) {

                    continue;

                }

                if (first) {

                    first = false;