ru.abyss.settings.importer.ImporterV13 Maven / Gradle / Ivy

Go to download
/*
 * 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);
	}

}