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

import org.gvsig.fmap.dal.ExpressionBuilder;

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.SQLConfig;

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.slf4j.Logger;

import org.slf4j.LoggerFactory;

public class SQLBuilderBase extends ExpressionBuilderBase 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 List<Parameter> parameters;

    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 TableNameBuilderBase implements TableNameBuilder {

        public String tableName;

        public String schemaName;

        private String databaseName;

        public TableNameBuilderBase() {

        }

        @Override

        public void accept(Visitor visitor, VisitorFilter filter) {

            if( filter.accept(this) ) {

                visitor.visit(this);

            }

        }

        @Override

        public TableNameBuilder database(String name) {

            this.databaseName = name;

            return this;

        }

        @Override

        public TableNameBuilder schema(String name) {

            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() {

            return !StringUtils.isEmpty(this.schemaName);

        }

        @Override

        public boolean has_database() {

            return !StringUtils.isEmpty(this.databaseName);

        }

        @Override

        public String toString() {

            if( this.has_database()) {

                if( this.has_schema()) {

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

                           identifier(this.schemaName) + "." + 

                           identifier(this.tableName);

                }

            } else {

                if( this.has_schema()) {

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

                           identifier(this.tableName);

                }                

            }

            return identifier(this.tableName);

        }

    }

    public class CountBuilderBase extends AbstractValue implements CountBuilder {

        protected Value value;

        protected boolean distinct;

        protected boolean all ;

        public CountBuilderBase() {

            this.value = null;

            this.distinct = false;

            this.all = false;

        }

        @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() {

            if( this.all ) {

                return MessageFormat.format(

                    config.getString(SQLConfig.count),

                    "*"

                );

            }

            if( this.distinct ) {

                return MessageFormat.format(

                    config.getString(SQLConfig.count_distinct),

                    value.toString()

                );

            }

            return MessageFormat.format(

                config.getString(SQLConfig.count),

                value.toString()

            );

        }

    }

    public class FromBuilderBase implements FromBuilder {

        protected TableNameBuilder tableName= null;

        private String subquery = null;

        private String passthrough = null;

        @Override

        public TableNameBuilder table() {

            if( tableName == null ) {

                this.tableName = new TableNameBuilderBase();

            }

            return this.tableName;

        }

        @Override

        public void accept(Visitor visitor, VisitorFilter filter) {

            if( filter.accept(this) ) {

                visitor.visit(this);

            }

            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() {

            if( ! StringUtils.isEmpty(passthrough) ) {

                return passthrough;

            }

            if( ! StringUtils.isEmpty(subquery) ) {

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

            }

            return this.tableName.toString();

        }

    }

    public class SelectColumnBuilderBase implements SelectColumnBuilder {

        private Variable name = null;

        private String alias = null;

        private Value value = null;

        private boolean asGeometry = false;

        @Override

        public void accept(Visitor visitor, VisitorFilter filter) {

            if( filter.accept(this) ) {

                visitor.visit(this);

            }

            if( this.name != null ) {

                this.name.accept(visitor, filter);

            }

            if( this.value != null ) {

                this.value.accept(visitor, filter);

            }

        }

        @Override

        public SelectColumnBuilder name(String name) {

            String quote = config.getString(Config.quote_for_identifiers);

            if (name.startsWith(quote)) {

                // Remove quotes

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

            }

            this.name = variable(name);

            this.value = null;

            this.asGeometry = false;

            return this;

        }

        @Override

        public SelectColumnBuilder all() {

            this.name = null;

            this.value = 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;

            this.name = null;

            return this;

        }

        @Override

        public SelectColumnBuilder as(String alias) {

            this.alias = alias;

            return this;

        }

        @Override

        public String getName() {

            return this.name.getName();

        }

        @Override

        public String getAlias() {

            return this.alias;

        }

        @Override

        public String getValue() {

            return this.alias;

        }

        @Override

        public String toString() {

            StringBuilder builder = new StringBuilder();

            if( this.asGeometry ) {

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

            } else {

                if( this.name != null ) {

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

                } else {

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

                }

            }

            if( this.alias != null ) {

                builder.append(" AS ");

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

            }

            return builder.toString();

        }

    }

    public class OrderByBuilderBase implements OrderByBuilder {

        protected String value;

        protected String custom;

        protected boolean ascending;

        public OrderByBuilderBase() {

        }

        @Override

        public void accept(Visitor visitor, VisitorFilter filter) {

            if( filter.accept(this) ) {

                visitor.visit(this);

            }

        }

        @Override

        public OrderByBuilder column(String name) {

            this.value = name;

            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 String toString() {

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

                return this.custom;

            }

            if( this.ascending ) {

                return this.value + "ASC";

            }

            return this.value + "DESC";

        }

    }

    public class SelectBuilderBase implements SelectBuilder {

        protected FromBuilder from;

        protected ExpressionBuilder where;

        protected long limit = -1;

        protected long offset = -1;

        protected List<SelectColumnBuilder> columns;

        protected List<OrderByBuilder> order_by;

        protected boolean distinct;

        public SelectBuilderBase() {

            this.columns = new ArrayList<>();

            this.distinct = false;

        }

        @Override

        public void accept(Visitor visitor, VisitorFilter filter) {

            if( filter.accept(this) ) {

                visitor.visit(this);

            }

            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);

                }

            }

        }

        @Override

        public SelectBuilder distinct() {

            this.distinct = true;

            return this;

        }

        @Override

        public SelectColumnBuilder column() {

            SelectColumnBuilder builder = createSelectColumnBuilder();

            this.columns.add(builder);

            return builder;

        }

        @Override

        public boolean has_column(String name) {

            for (SelectColumnBuilder 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 boolean has_from() {

            return this.from != null;

        }

        @Override

        public ExpressionBuilder where() {

            if (this.where == null) {

                this.where = createExpressionBuilder();

            }

            return this.where;

        }

        @Override

        public boolean has_where() {

            if( this.where == null ) {

                return false;

            }

            return this.where.getValue() != null;

        }

        @Override

        public SelectBuilder limit(long limit) {

            this.limit = limit;

            return this;

        }

        @Override

        public boolean has_limit() {

            return this.limit > 0;

        }

        @Override

        public SelectBuilder offset(long offset) {

            this.offset = offset;

            return this;

        }

        @Override

        public boolean has_offset() {

            return this.offset > 0;

        }

        @Override

        public OrderByBuilder order_by() {

            OrderByBuilder order = createOrderByBuilder();

            this.order_by.add(order);

            return order;

        }

        @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 ");

            if( this.distinct ) {

                builder.append("DISTINCT ");

            }

            boolean first = true;

            for (SelectColumnBuilder column : columns) {

                if (first) {

                    first = false;

                } else {

                    builder.append(", ");

                }

                builder.append(column.toString());

            }

            if ( this.has_from() ) {

                builder.append(" FROM ");

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

            }

            if ( this.has_where() ) {

                builder.append(" WHERE ");

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

            }

            if( this.has_order_by() ) {

                builder.append(" ORDER BY ");

                first = true;

                for (OrderByBuilder item : this.order_by) {

                    if (first) {

                        first = false;

                    } else {

                        builder.append(", ");

                    }

                    builder.append(item.toString());                    

                }   

            }

            if ( this.has_limit() ) {

                builder.append(" LIMIT ");

                builder.append(this.limit);

            }

            if ( this.has_offset() ) {

                builder.append(" OFFSET ");

                builder.append(this.offset);

            }

            return builder.toString();

        }

    }

    public class DropTableBuilderBase implements DropTableBuilder {

        protected TableNameBuilder table;

        @Override

        public TableNameBuilder table() {

            if( table == null ) {

                table = new TableNameBuilderBase();

            }

            return table;

        }

        @Override

        public void accept(Visitor visitor, VisitorFilter filter) {

            if( filter.accept(this) ) {

                visitor.visit(this);

            }

            this.table.accept(visitor,filter);

        }

        @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.getString(SQLConfig.DROP_TABLE_table),

                            this.table.toString()

                    )

            );

            String sql;

            if( config.has_functionality(SQLConfig.DELETE_GEOMETRY_COLUMN_FROM_TABLE_schema_table) ) {

                if (this.table.has_schema()) {

                    sql = MessageFormat.format(

                            config.getString(SQLConfig.DELETE_GEOMETRY_COLUMN_FROM_TABLE_schema_table),

                            string(this.table.getSchema()),

                            string(this.table.getName())

                    );

                } else {

                    sql = MessageFormat.format(

                            config.getString(SQLConfig.DELETE_GEOMETRY_COLUMN_FROM_TABLE_table),

                            identifier(this.table.getName())

                    );

                }

                if( !StringUtils.isEmpty(sql) ) {

                    sqls.add(sql);

                }

            }

            return sqls;

        }

    }

    public class GrantRoleBuilderBase implements GrantRoleBuilder {

        protected TableNameBuilder table;

        protected String role;

        protected Set<Privilege> privileges;

        public GrantRoleBuilderBase(TableNameBuilder table, String role) {

            this.table = table;

            this.role = role;

            this.privileges = new HashSet<>();

        }

        @Override

        public GrantRoleBuilder privilege(Privilege privilege) {

            privileges.add(privilege);

            return this;

        }

        @Override

        public GrantRoleBuilder select() {

             privileges.add(Privilege.SELECT);

            return this;

        }

        @Override

        public GrantRoleBuilder update() {

             privileges.add(Privilege.UPDATE);

            return this;

        }

        @Override

        public GrantRoleBuilder insert() {

            privileges.add(Privilege.INSERT);

            return this;

        }

        @Override

        public GrantRoleBuilder delete() {

            privileges.add(Privilege.DELETE);

            return this;

        }

        @Override

        public GrantRoleBuilder truncate() {

            privileges.add(Privilege.TRUNCATE);

            return this;

        }

        @Override

        public GrantRoleBuilder reference() {

            privileges.add(Privilege.REFERENCE);

            return this;

        }

        @Override

        public GrantRoleBuilder trigger() {

            privileges.add(Privilege.TRIGGER);

            return this;

        }

        @Override

        public GrantRoleBuilder all() {

            privileges.add(Privilege.ALL);

            return this;

        }

        protected String getPrivilegeName(Privilege privilege) {

            switch(privilege) {

                case DELETE:

                    return "DELETE";

                case INSERT:

                    return "INSERT";

                case REFERENCE:

                    return "REFERENCE";

                case SELECT:

                    return "SELECT";

                case TRIGGER:

                    return "TRIGGER";

                case TRUNCATE:

                    return "TRUNCATE";

                case UPDATE:

                    return "UPDATE";

                case ALL:

                default:

                    return "ALL";

            }

        }

        @Override

        public String toString() {

            StringBuilder builder = new StringBuilder();

            boolean first = true;

            for (Privilege privilege : privileges) {

                if (first) {

                    first = false;

                } else {

                    builder.append(", ");

                }

                builder.append( this.getPrivilegeName(privilege));

            }

            String sql = MessageFormat.format(

                    config.getString(SQLConfig.GRANT_privileges_ON_table_TO_role),

                    builder.toString(),

                    table.toString(),

                    role

            );

            return sql;

        }

    }

    public class GrantBuilderBase implements GrantBuilder {

        protected TableNameBuilder table;

        protected Map<String, GrantRoleBuilder> roles;

        public GrantBuilderBase() {

            this.roles = new HashMap<>();

        }

        @Override

        public TableNameBuilder table() {

            if( table == null ) {

                table = new TableNameBuilderBase();

            }

            return table;

        }

        @Override

        public void accept(Visitor visitor, VisitorFilter filter) {

            if( filter.accept(this) ) {

                visitor.visit(this);

            }

            if( this.table!= null ) {

                this.table.accept(visitor,filter);

            }

        }

        @Override

        public GrantRoleBuilder role(String role) {

            GrantRoleBuilder roleBuilder = this.roles.get(role);

            if( roleBuilder == null ) {

                roleBuilder = createGrantRoleBuilder(this.table(), role);

                this.roles.put(role, roleBuilder);

            }

            return roleBuilder;

        }

        @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<>();

            for (GrantRoleBuilder role : roles.values()) {

                sqls.add(role.toString());

            }

            return sqls;

        }

    }

    public class UpdateColumnBuilderBase extends InsertColumnBuilderBase implements UpdateColumnBuilder {

        public UpdateColumnBuilderBase() {

            super();

        }

        @Override

        public UpdateColumnBuilder name(String name) {

            return (UpdateColumnBuilder) super.name(name);

        }

        @Override

        public UpdateColumnBuilder with_value(Value value) {

            return (UpdateColumnBuilder) super.with_value(value);

        }

    }

    public class UpdateBuilderBase implements UpdateBuilder {

        protected ExpressionBuilder where;

        protected List<UpdateColumnBuilder> columns;

        protected TableNameBuilder table;

        public UpdateBuilderBase() {

            this.columns = new ArrayList<>();

        }

        @Override

        public void accept(Visitor visitor, VisitorFilter filter) {

            if( filter.accept(this) ) {

                visitor.visit(this);

            }

            if( this.table != null ) {

                this.table.accept(visitor, filter);

            }

            for (UpdateColumnBuilder column : columns) {

                column.accept(visitor, filter);

            }

            if( this.has_where() ) {

                this.where.accept(visitor, filter);

            }

        }

        @Override

        public ExpressionBuilder where() {

            if (this.where == null) {

                this.where = createExpressionBuilder();

            }

            return this.where;

        }

        @Override

        public TableNameBuilder table() {

            if( table == null ) {

                table = new TableNameBuilderBase();

            }

            return table;

        }

        @Override

        public UpdateColumnBuilder column() {

            UpdateColumnBuilder column = createUpdateColumnBuilder();

            this.columns.add(column);

            return column;

        }

        @Override

        public boolean has_where() {

            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 (UpdateColumnBuilder column : columns) {

                if (first) {

                    first = false;

                } else {

                    columnsAndValues.append(", ");

                }

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

                columnsAndValues.append(" = ");

                columnsAndValues.append(column.getValue().toString());

            }

            String sql;

            if ( this.has_where() ) {

                sql = MessageFormat.format(

                        config.getString(SQLConfig.UPDATE_table_SET_columnsAndValues_WHERE_expresion),

                        this.table.toString(),

                        columnsAndValues.toString(),

                        this.where.toString()

                );

            } else {

                sql = MessageFormat.format(

                        config.getString(SQLConfig.UPDATE_table_SET_columnsAndValues),

                        this.table.toString(),

                        columnsAndValues.toString()

                );

            }

            return sql;

        }

    }

    public class DeleteBuilderBase implements DeleteBuilder {

        protected ExpressionBuilder where;

        protected TableNameBuilder table;

        public DeleteBuilderBase() {

        }

        @Override

        public void accept(Visitor visitor, VisitorFilter filter) {

            if( filter.accept(this) ) {

                visitor.visit(this);

            }

            if( this.table != null ) {

                this.table.accept(visitor, filter);

            }

            if( this.has_where() ) {

                this.where.accept(visitor, filter);

            }

        }

        @Override

        public ExpressionBuilder where() {

            if (this.where == null) {

                this.where = createExpressionBuilder();

            }

            return this.where;

        }

        @Override

        public TableNameBuilder table() {

            if( table == null ) {

                table = new TableNameBuilderBase();

            }

            return table;

        }

        @Override

        public boolean has_where() {

            return this.where != null;

        }

        @Override

        public String toString() {

            /*

             * DELETE FROM table_name

             * WHERE some_column=some_value; 

             */

            String sql;

            if( this.has_where() ) {

                sql = MessageFormat.format(

                        config.getString(SQLConfig.DELETE_FROM_table_WHERE_expresion),

                        this.table.toString(),

                        this.where.toString()

                );

            } else {

                sql = MessageFormat.format(

                        config.getString(SQLConfig.DELETE_FROM_table),

                        this.table.toString()

                );

            }

            return sql;

        }

    }

    public class AlterTableBuilderBase implements AlterTableBuilder {

        protected TableNameBuilder 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 void accept(Visitor visitor, VisitorFilter filter) {

            if( filter.accept(this) ) {

                visitor.visit(this);

            }

            if( this.table != null ) {

                this.table.accept(visitor, filter);

            }

        }

        @Override

        public TableNameBuilder table() {

            if( table == null ) {

                table = new TableNameBuilderBase();

            }

            return table;

        }

        @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;

                } else {

                    builder.append("; ");

                }

                builder.append(sql);

            }

            return builder.toString();

        }

        @Override

        public List<String> toStrings() {

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

            /*

             ALTER TABLE [ ONLY ] name [ * ]

             action [, ... ]

             ALTER TABLE [ ONLY ] name [ * ]

             RENAME [ COLUMN ] column TO new_column

             ALTER TABLE name

             RENAME TO new_name

             ALTER TABLE name

             SET SCHEMA new_schema

             where action is one of:

             ADD [ COLUMN ] column data_type [ COLLATE collation ] [ column_constraint [ ... ] ]

             DROP [ COLUMN ] [ IF EXISTS ] column [ RESTRICT | CASCADE ]

             ALTER [ COLUMN ] column [ SET DATA ] TYPE data_type [ COLLATE collation ] [ USING expression ]

             ALTER [ COLUMN ] column SET DEFAULT expression

             ALTER [ COLUMN ] column DROP DEFAULT

             ALTER [ COLUMN ] column { SET | DROP } NOT NULL

             ALTER [ COLUMN ] column SET STATISTICS integer

             ALTER [ COLUMN ] column SET ( attribute_option = value [, ... ] )

             ALTER [ COLUMN ] column RESET ( attribute_option [, ... ] )

             ALTER [ COLUMN ] column SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN }

             ADD table_constraint [ NOT VALID ]

             ADD table_constraint_using_index

             VALIDATE CONSTRAINT constraint_name

             DROP CONSTRAINT [ IF EXISTS ]  constraint_name [ RESTRICT | CASCADE ]

             DISABLE TRIGGER [ trigger_name | ALL | USER ]

             ENABLE TRIGGER [ trigger_name | ALL | USER ]

             ENABLE REPLICA TRIGGER trigger_name

             ENABLE ALWAYS TRIGGER trigger_name

             DISABLE RULE rewrite_rule_name

             ENABLE RULE rewrite_rule_name

             ENABLE REPLICA RULE rewrite_rule_name

             ENABLE ALWAYS RULE rewrite_rule_name

             CLUSTER ON index_name

             SET WITHOUT CLUSTER

             SET WITH OIDS

             SET WITHOUT OIDS

             SET ( storage_parameter = value [, ... ] )

             RESET ( storage_parameter [, ... ] )

             INHERIT parent_table

             NO INHERIT parent_table

             OF type_name

             NOT OF

             OWNER TO new_owner

             SET TABLESPACE new_tablespace

             and table_constraint_using_index is:

             [ CONSTRAINT constraint_name ]

             { UNIQUE | PRIMARY KEY } USING INDEX index_name

             [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

             */

            StringBuilder builder = new StringBuilder();

            builder.append("ALTER TABLE");

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

            builder.append(" ");

            boolean first = true;

            for (String column : drops) {

                if (first) {

                    first = false;

                } else {

                    builder.append(", ");

                }

                builder.append("DROP COLUMN IF EXISTS ");

                builder.append(column);

            }

            first = drops.isEmpty();

            for (ColumnInfo column : adds) {

                if (first) {

                    first = false;

                } else {

                    builder.append(", ");

                }

                builder.append("ADD COLUMN ");

                builder.append(column.name);

                builder.append(" ");

                if( column.type == DataTypes.INT && column.isAutomatic ) {

                    builder.append(" SERIAL");

                } else {

                    builder.append(sqltype(column.type, column.type_p, column.type_s));

                }

                if (column.defaultValue == null) {

                    if (column.allowNulls) {

                        builder.append(" DEFAULT NULL");

                    }

                } else {

                    builder.append(" DEFAULT '");

                    builder.append(column.defaultValue.toString());

                    builder.append("'");

                }

                if (column.allowNulls) {

                    builder.append(" NULL");

                } else {

                    builder.append(" NOT NULL");

                }

                if (column.isPk) {

                    builder.append(" PRIMARY KEY");

                }

            }

            first = drops.isEmpty() && adds.isEmpty();

            for (ColumnInfo column : alters) {

                if (first) {

                    first = false;

                } else {

                    builder.append(", ");

                }

                builder.append("ALTER COLUMN ");

                builder.append(column.name);

                builder.append("SET DATA TYPE ");

                if( column.type == DataTypes.INT && column.isAutomatic ) {

                    builder.append(" SERIAL");

                } else {

                    builder.append(sqltype(column.type, column.type_p, column.type_s));

                }

                builder.append(", ");

                if (column.defaultValue == null) {

                    if (column.allowNulls) {

                        builder.append("ALTER COLUMN ");

                        builder.append(column.name);

                        builder.append(" SET DEFAULT NULL");

                    } else {

                        builder.append("ALTER COLUMN ");

                        builder.append(column.name);

                        builder.append(" DROP DEFAULT");

                    }

                } else {

                    builder.append("ALTER COLUMN ");

                    builder.append(column.name);

                    builder.append(" SET DEFAULT '");

                    builder.append(column.defaultValue.toString());

                    builder.append("'");

                }

            }

            first = drops.isEmpty() && adds.isEmpty() && alters.isEmpty();

            for (Pair pair : renames) {

                if (first) {

                    first = false;

                } else {

                    builder.append(", ");

                }

                builder.append("RENAME COLUMN ");

                builder.append(pair.getLeft());

                builder.append(" TO ");

                builder.append(pair.getRight());

            }

            sqls.add(builder.toString());

            return sqls;

        }

    }

    public class CreateTableBuilderBase implements CreateTableBuilder {

        protected TableNameBuilder table;

        protected List<ColumnInfo> columns;

        public CreateTableBuilderBase() {

            this.columns = new ArrayList<>();

        }

        @Override

        public void accept(Visitor visitor, VisitorFilter filter) {

            if( filter.accept(this) ) {

                visitor.visit(this);

            }

            if( this.table != null ) {

                this.table.accept(visitor, filter);

            }

        }

        @Override

        public TableNameBuilder table() {

            if( table == null ) {

                table = new TableNameBuilderBase();

            }

            return table;

        }

        @Override

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