ru.abyss.settings.importer.ImporterV13 Maven / Gradle / Ivy
Go to download
Show more of this group Show more artifacts with this name
Show all versions of abyss-export Show documentation
Show all versions of abyss-export Show documentation
DB import/export library for Abyss ACS
/*
* Copyright Бездна (c) 2019.
*/
package ru.abyss.settings.importer;
import java.sql.Connection;
import java.sql.Statement;
import ru.abyss.settings.ProgressCallback;
/**
* @author Minu <[email protected]>
* @since 13.03.2019 17:05:41
*/
@ImporterVersion("1.3")
public class ImporterV13 extends ImporterV14 {
@Override
public void doImport(Connection h2Connection, Connection pgConnection, Long factoryId, Long userId,
boolean isOwnFactory, boolean isLoadPrices, boolean isLoadConsistRemains, boolean isLoadContractors,
ProgressCallback callback) throws Exception {
// в версии 1.4 от таблицы settings.tclearance_groups отпочковалась таблица settings.tcorrection_groups
try (Statement st = h2Connection.createStatement()) {
// создаём новую таблицу
st.addBatch("create table settings.tcorrection_groups (id bigint not null, npp bigint, name varchar, correctionw double precision, correctionh double precision, PRIMARY KEY(id))");
st.addBatch("COMMENT ON COLUMN settings.tcorrection_groups.id IS 'bigserial'");
st.addBatch("COMMENT ON COLUMN settings.tcorrection_groups.npp IS 'int8'");
st.addBatch("COMMENT ON COLUMN settings.tcorrection_groups.name IS 'varchar'");
st.addBatch("COMMENT ON COLUMN settings.tcorrection_groups.correctionw IS 'float8'");
st.addBatch("COMMENT ON COLUMN settings.tcorrection_groups.correctionh IS 'float8'");
// заполняем новую таблицу данными
st.addBatch(
"with corrections as (" +
" select id, regexp_replace(cg.name, '\\(.*\\)', '(' || rtrim(rtrim(rtrim(cg.correctionw::text, '0'), ','), '.') " +
" || '/' || rtrim(rtrim(rtrim(cg.correctionh::text, '0'), ','), '.') || ')') as name," +
" cg.correctionw, cg.correctionh" +
" from settings.tclearance_groups cg" +
" where (abs(cg.correctionw) > 0.001) or (abs(cg.correctionh) > 0.001)" +
"),\n" +
"grouped as (" +
" select min(id) as id, cg.name, cg.correctionw, cg.correctionh " +
" from corrections cg " +
" group by cg.name, cg.correctionw, cg.correctionh" +
" order by cg.name" +
")\n" +
"insert into settings.tcorrection_groups (id, npp, name, correctionw, correctionh) " +
"select id, 10 * rownum(), cg.name, cg.correctionw, cg.correctionh " +
"from grouped cg");
// создаём связь материалов
st.addBatch("ALTER TABLE settings.tmaterials ADD COLUMN tcorrection_group_id BIGINT");
st.addBatch("COMMENT ON COLUMN settings.tmaterials.tcorrection_group_id IS 'int8'");
st.addBatch(
"ALTER TABLE settings.tmaterials" +
" ADD CONSTRAINT tmaterials_correction_group_fk FOREIGN KEY (tcorrection_group_id)" +
" REFERENCES settings.tcorrection_groups(id)" +
" ON DELETE SET NULL" +
" ON UPDATE CASCADE" +
" NOT DEFERRABLE");
// обновляем ссылки материалов
st.addBatch(
"update settings.tmaterials m " +
"set tcorrection_group_id = (select corr.id from settings.tclearance_groups cg join settings.tcorrection_groups corr " +
"on corr.correctionw = cg.correctionw and corr.correctionh = cg.correctionh " +
"and corr.name = regexp_replace(cg.name, '\\(.*\\)', '(' || rtrim(rtrim(rtrim(cg.correctionw::text, '0'), ','), '.') " +
" || '/' || rtrim(rtrim(rtrim(cg.correctionh::text, '0'), ','), '.') || ')') " +
"where cg.id = m.tclearance_group_id)");
// удаляем старые поля
st.addBatch("ALTER TABLE settings.tclearance_groups DROP COLUMN correctionw");
st.addBatch("ALTER TABLE settings.tclearance_groups DROP COLUMN correctionh");
st.executeBatch();
h2Connection.commit();
} catch (Exception e) {
h2Connection.rollback();
throw e;
}
super.doImport(h2Connection, pgConnection, factoryId, userId, isOwnFactory, isLoadPrices, isLoadConsistRemains, isLoadContractors, callback);
}
}