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.

[rahulstech]

basically you want a automigration feature. that is not available in ormlite. infact hardly do any light weight orm can provide this feature. but there is an work around i use. you need to create your own db version control.

1. create a table db_version(version integer not null unique).
2. put a version number in this table like 1
3. maintain a current_version property in java like

final int CURRENT_VERSION = 1;

4. create a method

void migrate(ConnectionSource source, int from, int to)
{ /* your migration logic from version from to version to will go here */ }

5. on opening the database read the version from db_version table and match with CURRENT_VERSION
6. if db_version and CURRENT version does match then call the migrate method migrate(getConnectionSource(),db_version,CURRENT_VERSION);
7. update the db_version to CURRENT_VERSION by executing an insert query

you can add a table using these methods

1. rename existsing table suffixing _tmp example users -> users_tmp
2. execute a creaate table method with new column
3. insert all the values from tmp table to newly created table. use default value for the new columns if columns are non null type
4. drop the _tmp table

to execute these queries use ConnectionSource executeStatement or execute method. i am not use the exact method name there is something like that to execute raw queries on connection source.