V13__YFG242BeforeAndAfter.java
package db.migration;
import db.BaseMigration;
import org.apache.log4j.Logger;
import org.flywaydb.core.api.migration.Context;
import java.math.BigDecimal;
import java.sql.ResultSet;
import java.sql.Statement;
public class V13__YFG242BeforeAndAfter extends BaseMigration {
Logger log = Logger.getLogger(V13__YFG242BeforeAndAfter.class);
public void migrate(Context context) throws Exception {
log.debug("------JFG-242 java migration ");
log.debug(context.getConnection().getClientInfo());
try (Statement select = context.getConnection().createStatement()) {
try (ResultSet rows = select.executeQuery("SELECT EXISTS (\n" +
" SELECT FROM \n" +
" pg_tables\n" +
" WHERE \n" +
" schemaname = 'public' AND \n" +
" tablename = 'CustomsDeclarationCostedValues'\n" +
" );")) {
while (rows.next()) {
boolean tableExists = rows.getBoolean(1);
if (!tableExists) {
//create table
try (Statement createTable = context.getConnection().createStatement()) {
String sql = "create table if not exists CustomsDeclarationCostedValues (" +
"id int8," +
"created timestamp without time zone," +
"updated timestamp without time zone," +
"amountDue numeric(27,10) default 0," +
"penaltiesDue numeric(27,10) default 0," +
"customsVat numeric(27,10) default 0," +
"customsDuty numeric(27,10) default 0," +
"customstotal numeric(27,10) default 0," +
"schedule1P2 numeric(27,10) default 0," +
"cif numeric(27,10) default 0," +
"primary key (id)" +
");" +
"\n" +
"alter table customsdeclarationcostedvalues add column if not exists type varchar(100);\n" +
"\n" +
"create table if not exists customsdeclaration_customsdeclarationcostedvalues(\n" +
" customsdeclaration_id int8 not null,\n" +
" costedvalues_id int8 not null,\n" +
" unique (costedvalues_id)\n" +
" );\n" +
"\n" +
"alter table customsdeclaration_customsdeclarationcostedvalues drop constraint if exists FK5D415F5BA4591C5x; " +
"alter table customsdeclaration_customsdeclarationcostedvalues\n" +
" add constraint FK5D415F5BA4591C5x\n" +
" foreign key (customsdeclaration_id)\n" +
" references customsdeclaration;\n" +
"\n" +
"alter table customsdeclaration_customsdeclarationcostedvalues drop constraint if exists FK5D415F5BEC3EB09x; " +
"alter table customsdeclaration_customsdeclarationcostedvalues\n" +
" add constraint FK5D415F5BEC3EB09x\n" +
" foreign key (costedvalues_id)\n" +
" references customsdeclarationcostedvalues;\n";
//"alter table CustomsDeclaration add column beforeCorrection_id bigint;" +
//"alter table CustomsDeclaration add column afterCorrection_id bigint;" +
//"ALTER TABLE CustomsDeclaration ADD CONSTRAINT beforeCorrection_fk FOREIGN KEY
// (beforeCorrection_id) REFERENCES CustomsDeclarationCostedValues(id);\n" +
//"ALTER TABLE CustomsDeclaration ADD CONSTRAINT afterCorrection_fk FOREIGN KEY (afterCorrection_id)
// REFERENCES CustomsDeclarationCostedValues(id);";
//log.debug(sql);
createTable.execute(sql);
}
//migrate data
migrateDate(context);
//drop columns
try (Statement dropColumns = context.getConnection().createStatement()) {
String sql = "ALTER TABLE customsdeclaration drop COLUMN if exists amountdueaftercorrection504;\n" +
"ALTER TABLE customsdeclaration drop COLUMN if exists penaltiesDueAfterCorrection504;\n" +
"ALTER TABLE customsdeclaration drop COLUMN if exists customsVatAfterCorrection504;\n" +
"ALTER TABLE customsdeclaration drop COLUMN if exists customsDutyAfterCorrection504;\n" +
"ALTER TABLE customsdeclaration drop COLUMN if exists customstotalbeforecorrection;" +
"ALTER TABLE customsdeclaration drop COLUMN if exists cifbeforecorrection;" +
"ALTER TABLE customsdeclaration drop COLUMN if exists customsvatbeforecorrection;" +
"ALTER TABLE customsdeclaration drop COLUMN if exists customsdutybeforecorrection;" +
"ALTER TABLE customsdeclaration drop COLUMN if exists schedule1p2beforecorrection;" +
"ALTER TABLE customsdeclaration drop COLUMN if exists amountduebeforecorrection;" +
"ALTER TABLE customsdeclaration drop COLUMN if exists schedule1P2AfterCorrection504;";
//log.debug(sql);
dropColumns.execute(sql);
}
}
}
}
} catch (Exception e) {
log.error("Migration failed");
e.printStackTrace();
}
}
private void migrateDate(Context context) throws Exception {
try (Statement select = context.getConnection().createStatement()) {
try (ResultSet rows = select.executeQuery("SELECT * FROM customsdeclaration ORDER BY id")) {
while (rows.next()) {
long id = rows.getLong("id");
BigDecimal customstotalbeforecorrection = rows.getBigDecimal("customstotalbeforecorrection");
BigDecimal customsvatbeforecorrection = rows.getBigDecimal("customsvatbeforecorrection");
BigDecimal cifbeforecorrection = rows.getBigDecimal("cifbeforecorrection");
BigDecimal customsdutybeforecorrection = rows.getBigDecimal("customsdutybeforecorrection");
BigDecimal schedule1p2beforecorrection = rows.getBigDecimal("schedule1p2beforecorrection");
BigDecimal amountduebeforecorrection = rows.getBigDecimal("amountduebeforecorrection");
BigDecimal amountdueaftercorrection = rows.getBigDecimal("amountdueaftercorrection");
BigDecimal penaltiesdueaftercorrection = rows.getBigDecimal("penaltiesdueaftercorrection");
BigDecimal penaltiesduebeforecorrection = rows.getBigDecimal("penaltiesduebeforecorrection");
//BigDecimal customstotalbeforecorrection = rows.getBigDecimal("customstotalbeforecorrection");
//BigDecimal customstotalbeforecorrection = rows.getBigDecimal("customstotalbeforecorrection");
//BigDecimal customstotalbeforecorrection = rows.getBigDecimal("customstotalbeforecorrection");
if (amountduebeforecorrection != null) {
log.debug("Migrating costs for declaration: " + id);
try (Statement update = context.getConnection().createStatement()) {
String sql = "with info_before as (\n" +
" INSERT INTO CustomsDeclarationCostedValues\n" +
" (id, created,updated,amountDue, penaltiesDue, customsVat, customsDuty, customsTotal, " +
"schedule1P2, cif, type)\n" +
" VALUES ((select nextval('hibernate_sequence')), now(), now()," +
amountduebeforecorrection + ", " +
penaltiesduebeforecorrection + ", " +
customsvatbeforecorrection + ", " +
customsdutybeforecorrection + ", " +
customstotalbeforecorrection + ", " +
schedule1p2beforecorrection + ", " +
cifbeforecorrection +", "+
"'BEFORE_CORRECTION' "+
" )\n" +
" returning id \n" +
")";
if (amountdueaftercorrection != null) {
sql = sql + ", \n" +
"info_after as (\n" +
" INSERT INTO CustomsDeclarationCostedValues\n" +
" (id,created,updated, amountDue, penaltiesDue, customsVat, customsDuty, customsTotal, schedule1P2, " +
"cif, type)\n" +
" VALUES ((select nextval('hibernate_sequence')), now(), now()," +
amountdueaftercorrection + ", " +
penaltiesdueaftercorrection + ", " +
"null, " +
"null, " +
"null, " +
"null, " +
"null, " +
"'AFTER_CORRECTION' "+
" )\n" +
" returning id \n" +
")\n";
}
if (amountduebeforecorrection != null)
sql = sql + ", before_insert as (insert into " +
"customsdeclaration_customsdeclarationcostedvalues(customsdeclaration_id, costedvalues_id) select "
+ id + ", info_before.id from info_before)\n";
if (amountdueaftercorrection != null)
sql = sql + "insert into customsdeclaration_customsdeclarationcostedvalues(customsdeclaration_id, costedvalues_id)" +
" select "
+ id + ", info_after.id from info_after;\n";
else
sql = sql + "select info_before.id from info_before;\n";
//log.debug(sql);
update.execute(sql);
}
}
}
}
}
}
@Override
public Integer getChecksum() {
try {
return new Long(checksum(this.getClass())).intValue();
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
}