3

I'm trying to use liquibase to change the layout of my DB, but and I have a question that is:

Lets say for example that my old DB has a table which had 2 columns (firstName, lastName) but my new DB has only one column for those two (userName).

How could I do this migration using liquibase and Spring. Because with the following logic I would lose the original values.

Ideally I would like to be able to call my java code to make the changes, event though in this case it's over engineering in other cases it may be required ;) 

<databaseChangeLog
    xmlns="http://www.liquibase.org/xml/ns/dbchangelog/1.1"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog/1.1
    http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-1.1.xsd">

    <changeSet author="gcardoso" id="2012082703">

        <dropColumn columnName="firstName" tableName="t_user"/>
        <dropColumn columnName="lastName" tableName="t_user"/>

        ?????? How to migrate the names ??????

        <addColumn tableName="t_user">
            <column name="userName" type="VARCHAR2(255,0)">
                <constraints nullable="false"/>
            </column>
        </addColumn>
    </changeSet>
</databaseChangeLog>
Petar Minchev
  • 46,889
  • 11
  • 103
  • 119
Gonçalo Cardoso
  • 2,253
  • 4
  • 34
  • 63

1 Answers1

4

You need a custom refactoring. There are two possibilities:

  • Custom SQL for changes that can be achieved with sql
  • Custom Refactoring class for more complex changes. With this approach you can use Java to implement your refactoring.

So you would

  1. Add the new column
  2. Migrate the data from the old columns to the new column with a custom refactoring change
  3. Drop the old columns

How to use a custom refactoring class with Spring JdbcTemplate

@Override
public void execute(Database database) throws CustomChangeException {
    JdbcConnection connection = (JdbcConnection) database.getConnection();
    DataSource dataSource = new SingleConnectionDataSource(connection.getUnderlyingConnection(), true);
    JdbcTemplate template = new JdbcTemplate(dataSource, false);
}
delkant
  • 2,304
  • 1
  • 29
  • 28
Christoph Leiter
  • 9,147
  • 4
  • 29
  • 37
  • I'm trying to use Custom Refactoring class but I'm unable to make a working example. I can reach the method "public void execute(Database arg0)" of my class but I'm unable to insert any value into the DB. Could you please provide a link to an example using Custom Refactoring class? Because the link present at the LiquiBase page doesn't work – Gonçalo Cardoso Aug 27 '12 at 16:51
  • I added example code in my answer. It uses Spring's JdbcTemplate because you asked how to do this with Spring. But you could of course just use the `Connection` to work with plain JDBC. – Christoph Leiter Aug 27 '12 at 17:06
  • Thanks for the update, I'm trying to implement an example using QueryDSL with JdbcTemplate or plain JDBC, because I'm trying to make my all application "SQL free" – Gonçalo Cardoso Aug 27 '12 at 17:36
  • That's usually a good thing, but you really should use SQL for data migrations. You can't rely on any entities (via JPA or QueryDSL) because they may (and probably will) change in the future and so your migration scripts (which rely on the old structure, but the structure changed!) break. – Christoph Leiter Aug 27 '12 at 17:39