Auto add columns.

[Rollczi]

[00:02:19 WARN]: java.sql.SQLException: Column 'name2' not found.

is there an option to automatically add new columns?

[noordawod]

What do you mean?

PS: Have you considered designing your database in a Yaml file and then use code generation to create the actual code?

[j256]

Yeah right now we don't have a SQL update mechanism. It would need both the previous and new versions of the fields.

Actually @noordawod, we could do this if we added some sort of revision number. So each field would have a int revNumber and the user would be able to ask for a list of field adds and drops when going from rev X to rev Y. That would be cool. I guess changing a field would be difficult. So if you changed a field from and int to a long and increased the revNumber then it would get confused. Hrmmmmm. Yeah add and drops are easy but any sort of alter isn't immediately apparent how it would be done.

[Rollczi]

PS: Have you considered designing your database in a Yaml file and then use code generation to create the actual code?

no

[Rollczi]

I am currently using this method to add new columns. It is relatively safe because it does not remove anything, it only adds columns when it is needed.

Yes this code is kinda shitty, but it is only temporary solution.

    public <T> void updateColumns(Class<T> tClass) {
        this.action(tClass, (dao, connectionSource) -> {
            TableInfo<T, Object> tableInfo = dao.getTableInfo();
            DatabaseType databaseType = connectionSource.getDatabaseType();
            Map<String, String> columns = new HashMap<>();

            for (FieldType fieldType : tableInfo.getFieldTypes()) {
                if (fieldType.isForeignCollection()) {
                    continue;
                }

                String columnDefinition = fieldType.getColumnDefinition();
                StringBuilder sb = new StringBuilder();

                if (columnDefinition == null) {
                    databaseType.appendColumnArg(tableInfo.getTableName(), sb, fieldType, Collections.emptyList(), Collections.emptyList(), Collections.emptyList(), Collections.emptyList());
                } else {
                    databaseType.appendEscapedEntityName(sb, fieldType.getColumnName());
                    sb.append(' ').append(columnDefinition).append(' ');
                }

                Object defaultValue = fieldType.getDefaultValue();

                if (defaultValue != null) {
                    sb.append("DEFAULT").append('(').append(defaultValue).append(')');
                }

                columns.put(fieldType.getColumnName(), sb.toString());
            }

            try (DatabaseConnection dbConnection = dao.startThreadConnection(); Connection connection = dbConnection.getUnderlyingConnection()) {

                for (Map.Entry<String, String> entry : columns.entrySet()) {
                    String column = entry.getKey();
                    String columnAndType = entry.getValue();

                    try (ResultSet tables = connection.getMetaData().getColumns(null, null, dao.getTableName(), column)) {
                        if (tables.next()) {
                            continue;
                        }

                        try (Statement statement = connection.createStatement()) {
                            statement.executeUpdate("ALTER TABLE `" + dao.getTableName() + "` ADD " + columnAndType);
                        }
                    }
                }

            } catch (Exception exception) {
                exception.printStackTrace();
            }
        });
    }

[j256]

Yeah that's a good idea as well @Rollczi . Get all of the columns in the database and add any that aren't there. I'm not sure if it would be possible to change the types of the columns however.

[Rollczi]

Changing column type is dangerous idea. I propose to add only feature to automatically add columns if it doesn't exists.