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

Go to download
/*
 * Copyright Бездна (c) 2018.
 */
package ru.abyss.settings.importer;

import java.io.IOException;
import java.io.InputStream;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Objects;
import java.util.Set;
import java.util.stream.Collectors;
import java.util.stream.Stream;

import org.postgresql.copy.CopyManager;
import org.postgresql.core.BaseConnection;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.jolbox.bonecp.ConnectionHandle;

import ru.abyss.settings.ProgressCallback;

/**
 * @author Minu <[email protected]>
 * @since 05.06.2018 12:20:11
 */
@ImporterVersion("1.14")
public class ImporterCurrent implements Importer {

	private static Logger logger = LoggerFactory.getLogger(ImporterCurrent.class);

	protected static final transient String ID = "id";
	private static final transient String SLAVE_ID = "slave_id";
	private static final transient String TFACTORY_ID = "tfactory_id";
	private static final transient String TMP_TABLE = "tmp_import";

	private boolean isColorsImported = false;
	private boolean isCustomersImported = false;
	private boolean isSuppliersImported = false;
	protected boolean isLoadPrices;
	protected boolean isLoadConsistRemains;
	protected boolean isLoadContractors;

	private static String castType(String type) throws Exception {
		switch (type.toLowerCase()) {
		case "bigserial":
			return "bigint";
		default:
			return type;
		}
	}

	public static String joinToStr(ResultSet row, String separator) throws SQLException {
		StringBuilder ret = new StringBuilder();
		int size = row.getMetaData().getColumnCount();
		for (int i = 1; i <= size; i++) {
			Object param = row.getObject(i);
			if (param != null) {
				if (param instanceof Float) {
					float v = (Float) param;	// для вещественных чисел с целыми значениями отрезаем дробную часть
					if (v == (int) v)
						ret.append(separator).append((int) v);
					else
						ret.append(separator).append(v);
				} else if (param instanceof Double) {
					double v = (Double) param;	// для вещественных чисел с целыми значениями отрезаем дробную часть
					if (v == (long) v)
						ret.append(separator).append((long) v);
					else
						ret.append(separator).append(v);
				} else if (param instanceof Enum) {
					ret.append(separator).append(((Enum) param).name());
				} else
					ret.append(separator).append(param.toString().replace("\\", "\\\\").replace("\r", "\\r").replace("\n", "\\n").replace("\t", "\\t"));
			} else
				ret.append(separator).append(param);
		}
		int sepLength = separator.length();
		return (ret.length() > sepLength) ? ret.substring(sepLength) : "";
	}

	private static void createTempTable(Connection pgConnect, ImportTable table) throws Exception {
    	logger.debug("create temp table: " + table);
		StringBuilder sql = new StringBuilder("create temp table ").append(TMP_TABLE).append(" (");
	    try (Statement st = pgConnect.createStatement()) {
	    	for (TableColumn col : table.getColumns())
            	sql.append(col.getName()).append(" ").append(castType(col.getType())).append((col.isNullable() ? "" : " not null")).append(", ");
            sql.append("PRIMARY KEY(").append(table.getPrimaryKey().stream().collect(Collectors.joining(", "))).append(")) on commit drop");

			st.execute(sql.toString());
		}
	}

	private static void fillTempTable(Connection pgConnect, Connection h2Connect, ImportTable table) throws Exception {
		createTempTable(pgConnect, table);
		String columns = table.getColumns().stream().map(col -> {
			if ("SETTINGS.TCNC".equalsIgnoreCase(table.toString()) && "interval".equalsIgnoreCase(col.getName()))
				return "\"" + col.getName().toUpperCase() + "\"";
			return col.getName();
		}).collect(Collectors.joining(", "));
		try (Statement selectStmt = h2Connect.createStatement();
				ResultSet rset = selectStmt.executeQuery("select " + columns + " from " + table)) {
        	logger.debug("fill temp table: " + table);

        	// создаём входящий поток данных на основе результата запроса 'select * from table'
        	InputStream is = new InputStream() {
				private byte[] buf;
				private int cur = 0;

				@Override
				public int read() throws IOException {
					if ((buf == null) || (cur >= buf.length))
						try {
							if (rset.next()) {
								String row = joinToStr(rset, "\t") + System.lineSeparator();
								buf = row.getBytes("UTF-8");
								cur = 0;
							} else
								buf = null;
						} catch (Exception e) {
							throw new IOException(e);
						}
					return (buf != null) ? buf[cur++] : -1;
				}
			};
			try (InputStream data = is) {
				// выполняем запрос копирования данных из InputStream во временную таблицу постгреса
				String query = new StringBuilder("COPY ").append(TMP_TABLE).append(" (")
						.append(columns.replace("\"", "")).append(") FROM STDIN WITH DELIMITER '\t' NUll 'null'").toString();
				CopyManager copyManager = new CopyManager((BaseConnection) ((pgConnect instanceof ConnectionHandle) ? ((ConnectionHandle) pgConnect).getInternalConnection() : pgConnect));
				copyManager.copyIn(query, data);
			}
		}
	}

	private String getPKeyEquality(ImportTable table, boolean isCompareById) {
		// формируем условие равенства первичных ключей
		return table.getPrimaryKey().stream().map(field -> {
				// ID в локальной таблице - это SLAVE_ID в глобальной,
				// но при импорте своих настроек на свой же завод это игнорируется
				return "src." + getSrcFieldMapper(table, field) + " = dest." + (ID.equalsIgnoreCase(field) && !isCompareById ? SLAVE_ID : getDestFieldMapper(table, field));
			}).collect(Collectors.joining(" and "));
	}

	private String getSrcPKeyIsNull(ImportTable table) {
		// формируем условие пустоты первичного ключа локальной таблицы
		return table.getPrimaryKey().stream().map(field -> "src." + getSrcFieldMapper(table, field) + " is null").collect(Collectors.joining(" and "));
	}

	private String getDestPKeyIsNull(ImportTable table) {
		// формируем условие пустоты первичного ключа локальной таблицы
		return table.getPrimaryKey().stream().map(field -> "dest." + getDestFieldMapper(table, field) + " is null").collect(Collectors.joining(" and "));
	}

	private String getSetFields(ImportTable table) {
		// формируем выражение присвоения значений полей глобальной таблицы
		return table.getColumns().stream().map(TableColumn::getName)
				.filter(field -> !table.getPrimaryKey().contains(field) && !ID.equalsIgnoreCase(field))
				.map(field -> getDestFieldMapper(table, field) + " = src." + getSrcFieldMapper(table, field)).collect(Collectors.joining(", "));
	}

	private String getDistinctFields(ImportTable table) {
		// формируем условие отличия значений полей
		return table.getColumns().stream().map(TableColumn::getName)
				.filter(field -> !table.getPrimaryKey().contains(field) && !ID.equalsIgnoreCase(field))
				.map(field -> "src." + getSrcFieldMapper(table, field) + " is distinct from dest." + getDestFieldMapper(table, field)).collect(Collectors.joining(" or "));
	}

	private String getSrcFields(ImportTable table) {
		// формируем список полей источника
		Stream ret = table.getColumns().stream().map(TableColumn::getName);
		if (!table.isIDPrimaryKey())
			ret = ret.filter(field -> !ID.equalsIgnoreCase(field));
		return ret.map(field -> "src." + getSrcFieldMapper(table, field)).collect(Collectors.joining(", "));
	}

	private String getDestFields(ImportTable table) {
		// формируем список полей приёмника
		Stream ret = table.getColumns().stream().map(TableColumn::getName);
		if (!table.isIDPrimaryKey())
			ret = ret.filter(field -> !ID.equalsIgnoreCase(field));
		return ret.map(field -> (ID.equalsIgnoreCase(field) ? SLAVE_ID : getDestFieldMapper(table, field))).collect(Collectors.joining(", "));
	}

	private void refreshDependencies(Connection pgConnect, Connection h2Connect, ImportTable table, Long factoryId, String pkey, ProgressCallback callback) throws Exception {
		boolean isContractors = "CONTRACTORS.TCUSTOMERS".equalsIgnoreCase(table.toString())
				|| "CONTRACTORS.TSUPPLIERS".equalsIgnoreCase(table.toString());
		boolean isParents = "SETTINGS.TCATALOG_NAMES".equalsIgnoreCase(table.toString())
				|| "SETTINGS.TELEMENTS".equalsIgnoreCase(table.toString());
		boolean isColors = "STOCK.TCOLORS".equalsIgnoreCase(table.toString());
		// КОСТЫЛЬ если запись ссылается сама на себя, то H2 не может выполнить ON UPDATE CASCADE
		List insideColorIds = new ArrayList<>();
		List outsideColorIds = new ArrayList<>();
		if (isColors && !isColorsImported) {
			try (PreparedStatement stmt = h2Connect.prepareStatement("select id from " + table + " where id = outside_tcolor_id")) {
				stmt.execute();
				try (ResultSet rs = stmt.getResultSet()) {
					while (rs.next())
						outsideColorIds.add(rs.getLong(1));
				}
			}
			try (PreparedStatement stmt = h2Connect.prepareStatement("select id from " + table + " where id = inside_tcolor_id")) {
				stmt.execute();
				try (ResultSet rs = stmt.getResultSet()) {
					while (rs.next())
						insideColorIds.add(rs.getLong(1));
				}
			}
		}
		try (PreparedStatement stmt = pgConnect.prepareStatement("select dest.id, src.id, count(1) over () as join_cnt, "
						+ "(select count(1) from " + TMP_TABLE + ") as src_cnt from " + table + " dest join " + TMP_TABLE + " src on " + pkey
						+ " where not dest.del and dest." + TFACTORY_ID + " = ?")) {
			stmt.setLong(1, factoryId);
			stmt.execute();
			try (ResultSet rs = stmt.getResultSet();
					PreparedStatement h2Stmt = h2Connect.prepareStatement("update " + table + " set " + ID + " = ? where " + ID + " = ?");
					PreparedStatement addressStmt = isContractors ? h2Connect.prepareStatement("update CONTRACTORS.TADDRESSES set tcounterparty_id = ? where tcounterparty_id = ?") : null;
					PreparedStatement directionsStmt = isContractors ? h2Connect.prepareStatement("update CONTRACTORS.TDIRECTIONS set tcounterparty_id = ? where tcounterparty_id = ?") : null;
					PreparedStatement parentsStmt = isParents ? h2Connect.prepareStatement("update SETTINGS.TPARENTS set parent_id = ? where parent_id = ?") : null) {
				int count = 0;
				final int batchSize = 1000;
				int dots = 0;
				while (rs.next()) {
					if (!Objects.equals(rs.getLong(3), rs.getLong(4)))
						throw new Exception("Ошибка контрольной суммы при импорте " + table);
					h2Stmt.setLong(1, rs.getLong(1));
					h2Stmt.setLong(2, rs.getLong(2));
					h2Stmt.addBatch();
	
					if (isContractors) {
						addressStmt.setLong(1, rs.getLong(1));
						addressStmt.setLong(2, rs.getLong(2));
						addressStmt.addBatch();
						directionsStmt.setLong(1, rs.getLong(1));
						directionsStmt.setLong(2, rs.getLong(2));
						directionsStmt.addBatch();
					}
					if (isParents) {
						parentsStmt.setLong(1, rs.getLong(1));
						parentsStmt.setLong(2, rs.getLong(2));
						parentsStmt.addBatch();
					}
	
					if (++count % batchSize == 0) {
						h2Stmt.executeBatch();
						if (isContractors) {
							addressStmt.executeBatch();
							directionsStmt.executeBatch();
						}
						if (isParents)
							parentsStmt.executeBatch();
						if (callback != null)
							for (; dots < 7.0 * count / rs.getLong(3); dots++)
								callback.setProgress(".", 0, 0);
					}
				}
				h2Stmt.executeBatch();	// добиваем оставшиеся записи
				if (isContractors) {
					addressStmt.executeBatch();
					directionsStmt.executeBatch();
				}
				if (isParents)
					parentsStmt.executeBatch();
				if (callback != null)
					for (; dots < 7; dots++)
						callback.setProgress(".", 0, 0);
			}
		}
		if (isColors && !isColorsImported) {
			if (!outsideColorIds.isEmpty())
				try (PreparedStatement stmt = h2Connect.prepareStatement("update " + table + " set outside_tcolor_id = " + ID + " where outside_tcolor_id = ?")) {
					for (Long id : outsideColorIds) {
						stmt.setLong(1, id);
						stmt.addBatch();
					}
					stmt.executeBatch();
				}
			if (!insideColorIds.isEmpty())
				try (PreparedStatement stmt = h2Connect.prepareStatement("update " + table + " set inside_tcolor_id = " + ID + " where inside_tcolor_id = ?")) {
					for (Long id : insideColorIds) {
						stmt.setLong(1, id);
						stmt.addBatch();
					}
					stmt.executeBatch();
				}
		}
	}

	private int[] importTable(Connection pgConnect, Connection h2Connect, ImportTable table, Long factoryId, Long userId, boolean isOwnFactory,
			boolean doAdd, boolean doUpdate, boolean doDelete, ProgressCallback callback) throws Exception {
    	logger.debug("import table: " + table);
		int[] tableCnts = {0, 0, 0};
		fillTempTable(pgConnect, h2Connect, table);
		boolean isCustomers = "CONTRACTORS.TCUSTOMERS".equalsIgnoreCase(table.toString());
		boolean isSuppliers = "CONTRACTORS.TSUPPLIERS".equalsIgnoreCase(table.toString());
		boolean isColors = "STOCK.TCOLORS".equalsIgnoreCase(table.toString());
		String pkey = getPKeyEquality(table, (isCustomers && isCustomersImported) || (isSuppliers && isSuppliersImported) || (isColors && isColorsImported));

		// если импортируем свою же организацию, то закидываем ID в SLAVE_ID, но только для тех, у кого SLAVE_ID другой организации
		// это условие работает когда после выгрузки из таблицы удаляют записи, если безусловно перезаписывать SLAVE_ID, то записи будут бесконечно создаваться при каждом импорте
		// TODO если выкачать базу - удалить запись - закачать базу - выкачать базу - закачать базу, то один раз всётаки происходит пересоздание
		// TODO если до этого загружалась база из 57й, то возможны конфликты уникальности SLAVE_ID
		if (isOwnFactory && table.isIDPrimaryKey()) {
			StringBuilder sql = new StringBuilder("update ").append(table).append(" dest set ").append(SLAVE_ID).append(" = dest.").append(ID)
					.append(" where not dest.del and dest.").append(TFACTORY_ID).append(" = ? and not exists(select 1 from ")
					.append(table).append(" src where src.").append(ID).append(" = dest.")
					.append(SLAVE_ID).append(" and src.").append(TFACTORY_ID).append(" = dest.").append(TFACTORY_ID).append(")");
			try (PreparedStatement st = pgConnect.prepareStatement(sql.toString())) {
				st.setLong(1, factoryId);
				st.executeUpdate();
			}
		}

		// Удаляем все записи, которых нет в источнике
		if (doDelete) {
			try (PreparedStatement stmt = pgConnect.prepareStatement("update only " + table
					+ " set del = true, date_update = now(), last_tuser_id = ?" 
					+ " where id in (select dest.id from only " + table + " dest left join " + TMP_TABLE + " src on " + pkey 
									+ " where not dest.del and dest." + TFACTORY_ID + " = ? and " + getSrcPKeyIsNull(table) + ")")) {
				stmt.setLong(1, userId);
				stmt.setLong(2, factoryId);
				tableCnts[2] = stmt.executeUpdate();
				logger.debug("deleted: " + tableCnts[2]);
			}
		}
		if (callback != null)
			callback.setProgress(".", 0, 0);

		// Обновляем существующие записи
		String setExpr = getSetFields(table);
		if (doUpdate && !setExpr.isEmpty()) {
			try (PreparedStatement stmt = pgConnect.prepareStatement("update only " + table + " dest"
					+ " set date_update = now(), last_tuser_id = ?, " + setExpr
					+ " from " + TMP_TABLE + " src"
					+ " where not dest.del and dest." + TFACTORY_ID + " = ? and " + pkey
						+ " and (" + getDistinctFields(table) + ")")) {
				stmt.setLong(1, userId);
				stmt.setLong(2, factoryId);
				tableCnts[1] = stmt.executeUpdate();
				logger.debug("updated: " + tableCnts[1]);
			}
		}
		if (callback != null)
			callback.setProgress(".", 0, 0);

		// Добавляем новые записи
		if (doAdd) {
			try (PreparedStatement stmt = pgConnect.prepareStatement("insert into " + table + " (last_tuser_id, " + TFACTORY_ID + ", " + getDestFields(table) + ")"
					+ " select ?, ?, " + getSrcFields(table)
					+ " from " + TMP_TABLE + " src left join only " + table + " dest"
					+ " on not dest.del and dest." + TFACTORY_ID + " = ? and " + pkey
					+ " where dest." + TFACTORY_ID + " is null and " + getDestPKeyIsNull(table))) {
				stmt.setLong(1, userId);
				stmt.setLong(2, factoryId);
				stmt.setLong(3, factoryId);
				tableCnts[0] = stmt.executeUpdate();
				logger.debug("added: " + tableCnts[0]);
			}
		}
		if (callback != null)
			callback.setProgress(".", 0, 0);

		// Обновляем id'шки в локальных таблицах
    	logger.debug("refresh: " + table);
		refreshDependencies(pgConnect, h2Connect, table, factoryId, pkey, callback);

		isCustomersImported |= isCustomers;
		isSuppliersImported |= isSuppliers;
		isColorsImported |= isColors;

    	logger.debug("done: " + table);
		if (callback != null)
			callback.setProgress(" ", 0, 0);
		return tableCnts;
	}

	private int[] importTable(Connection pgConnect, Connection h2Connect, ImportTable table, Long factoryId, Long userId, boolean isOwnFactory,
			boolean isLoadPrices, boolean isLoadContractors, ProgressCallback callback) throws Exception {
		boolean doAdd = true;
		boolean doUpdate = true;
		boolean doDelete = true;
		return importTable(pgConnect, h2Connect, table, factoryId, userId, isOwnFactory, doAdd, doUpdate, doDelete, callback);
	}

	private static void createDependencies(Map tables, DatabaseMetaData metaData, boolean isLoadPrices, boolean isLoadContractors) throws Exception {
		ImportTable cust = tables.get("CONTRACTORS.TCUSTOMERS");
		ImportTable supl = tables.get("CONTRACTORS.TSUPPLIERS");
		for (ImportTable tbl : tables.values())
			try (ResultSet rs = metaData.getImportedKeys(null, tbl.getScheme(), tbl.getTable())) {
				while (rs.next()) {
	            	String schema = rs.getString("PKTABLE_SCHEM");
					String fkTableName = schema + "." + rs.getString("PKTABLE_NAME");
	            	if (("CONTRACTORS".equalsIgnoreCase(schema) && !isLoadContractors)
	            			|| ("CATALOG.TSERVICES".equalsIgnoreCase(fkTableName) && !isLoadPrices))
	            		continue;
					ImportTable ftbl = tables.get(fkTableName);
					if (ftbl == null)
						throw new Exception("Зависимая таблица " + fkTableName + " не найдена");
					tbl.addDependency(ftbl);
				}
				// у TDIRECTIONS есть неявная зависимость от поставщиков и заказчиков, добавляем её для правильной сортировки
				if ("CONTRACTORS.TDIRECTIONS".equalsIgnoreCase(tbl.toString()) && (cust != null) && (supl != null)) {
					tbl.addDependency(cust);
					tbl.addDependency(supl);
				}
				// у TPARENTS есть неявная зависимость от названий и элементов, добавляем её для правильной сортировки
				if ("SETTINGS.TPARENTS".equalsIgnoreCase(tbl.toString())) {
					tbl.addDependency(tables.get("SETTINGS.TCATALOG_NAMES"));
					tbl.addDependency(tables.get("SETTINGS.TELEMENTS"));
				}
			}

		if ((cust != null) && (supl != null)) {
			// переворачиваем зависимость CONTRACTORS.TADDRESSES от контрагентов в обратную сторону
			// таким образом разруливаем циклическую зависимость между этими таблицами
			// сначала импортируем контрагентов без адресов, потом адреса, потом ещё раз контрагентов только с адресами
			ImportTable adr = tables.get("CONTRACTORS.TADDRESSES");
			cust.removeDependency(adr);
			supl.removeDependency(adr);
			TableColumn id = cust.getColumn("ID");
			TableColumn aid = cust.getColumn("TADDRESS_ID");
			cust.removeColumn(aid.getName());
			supl.removeColumn(aid.getName());
			adr.addDependency(cust);
			adr.addDependency(supl);

			ImportTable custAdr = new ImportTable("CONTRACTORS", "TCUSTOMERS");
			custAdr.setPrimaryKey(cust.getPrimaryKey());
			custAdr.addDependency(adr);
			custAdr.addColumn(id);
			custAdr.addColumn(aid);
			tables.put("CONTRACTORS.TCUSTOMERS_ADR", custAdr);

			ImportTable suplAdr = new ImportTable("CONTRACTORS", "TSUPPLIERS");
			suplAdr.setPrimaryKey(supl.getPrimaryKey());
			suplAdr.addDependency(adr);
			suplAdr.addColumn(id);
			suplAdr.addColumn(aid);
			tables.put("CONTRACTORS.TSUPPLIERS_ADR", suplAdr);
		}
	}

	@Override
	public void doImport(Connection h2Connection, Connection pgConnection, Long factoryId, Long userId, boolean isOwnFactory,
			boolean isLoadPrices, boolean isLoadConsistRemains, boolean isLoadContractors, ProgressCallback callback) throws Exception {
		this.isLoadPrices = isLoadPrices;
		this.isLoadConsistRemains = isLoadConsistRemains;
		this.isLoadContractors = isLoadContractors;
		Map tables = new HashMap<>();
		DatabaseMetaData metaData = h2Connection.getMetaData();

		// очищаем таблицу счётчиков
		try (CallableStatement stmt = pgConnection.prepareCall("{ call import57.clear_counter(?) }")) {
			stmt.setLong(1, factoryId);
			stmt.execute();
			pgConnection.commit();
		} catch (Exception e) {
			pgConnection.rollback();
			throw e;
		}

		// достаём список таблиц
		try (ResultSet rs = metaData.getTables(null, null, null, new String[] {"TABLE"})) {
			while (rs.next()) {
				String name = rs.getString("TABLE_NAME");
				String schem = rs.getString("TABLE_SCHEM");
				if ("PUBLIC".equalsIgnoreCase(schem) && "VERSION".equalsIgnoreCase(name))
					continue;

				ImportTable tbl = new ImportTable(schem, name);

				// достаём первичный ключ
				try (ResultSet rs2 = metaData.getPrimaryKeys(null, schem, name)) {
					while (rs2.next())
						tbl.addPrimaryKey(rs2.getString("COLUMN_NAME"));
				}

			    try (ResultSet rs2 = metaData.getColumns(null, schem, name, null)) {
			    	while (rs2.next())
			    		tbl.addColumn(rs2.getString("COLUMN_NAME"), rs2.getString("REMARKS"), "YES".equalsIgnoreCase(rs2.getString("IS_NULLABLE")));
			    }

			    tbl = filterTable(h2Connection, pgConnection, tbl);
			    if (tbl != null)
			    	tables.put(tbl.toString(), tbl);
			}
		}

		// достаём связи таблиц
		createDependencies(tables, metaData, isLoadPrices, isLoadContractors);

		// производим топологическую сортировку по зависимостям таблиц
		Set used = new HashSet<>();
		List> sorted = new ArrayList<>();
		do {
			List newLevel = tables.values().stream()
					.filter(tbl -> !used.contains(tbl)			// исключаем цикличные зависимости
							&& used.containsAll(tbl.getDependencies().stream().filter(d -> d != tbl).collect(Collectors.toSet())))
					.collect(Collectors.toList());
			if (!newLevel.isEmpty()) {
				used.addAll(newLevel);
				sorted.add(newLevel);
			} else
				break;
		} while (true);
		Set remaining = new HashSet<>(tables.values());
		remaining.removeAll(used);
		if (!remaining.isEmpty())
			throw new Exception("Обнаружена циклическая зависимость в таблицах: " + remaining);

		// отдельно обрабатываем таблицу stock.tcolors, тк у неё есть ссылки самой на себя
		// создаём копию этой таблицы, таким образом чтобы сначала импортировать данные, а потом цикличные ссылки
		for (int i = 0; i < sorted.size(); i++) {
			List tbls = sorted.get(i);
			ImportTable colors = tbls.stream().filter(tbl -> "STOCK.TCOLORS".equalsIgnoreCase(tbl.toString())).findFirst().orElse(null);
			if (colors != null) {
				ImportTable colors2 = new ImportTable(colors.getScheme(), colors.getTable());
				colors2.setPrimaryKey(colors.getPrimaryKey());
				colors2.addColumn(colors.getColumn("ID"));
				colors2.addColumn(colors.getColumn("OUTSIDE_TCOLOR_ID"));
				colors2.addColumn(colors.getColumn("INSIDE_TCOLOR_ID"));
				colors2.addDependency(colors);
				colors.removeColumn("OUTSIDE_TCOLOR_ID");
				colors.removeColumn("INSIDE_TCOLOR_ID");
				colors.removeDependency(colors);

				sorted.add(i + 1, Arrays.asList(colors2));
				break;
			}
		}

		// импортируем данные
		long i = 0;
		for (List tbls : sorted)
			for (ImportTable table : tbls)
				try {
					if (callback != null)
						callback.setProgress("импорт " + table.getTable().toLowerCase() + " ", 0, -1);
					int[] tableCnts = importTable(pgConnection, h2Connection, table, factoryId,
							userId, isOwnFactory, isLoadPrices, isLoadContractors, callback);
					// обновляем счётчики
					try (PreparedStatement stmt = pgConnection.prepareStatement("update import57.tcounts set adds = adds + ?, upds = upds + ?, dels = dels + ? where " + TFACTORY_ID + " = ?")) {
						stmt.setInt(1, tableCnts[0]);
						stmt.setInt(2, tableCnts[1]);
						stmt.setInt(3, tableCnts[2]);
						stmt.setLong(4, factoryId);
						stmt.execute();
					}
					pgConnection.commit();
					h2Connection.commit();
					if (callback != null)
						callback.setProgress("(A: " + tableCnts[0] + ", U: " + tableCnts[1] + ", D: " + tableCnts[2] + ")", ++i, used.size() + 1);	// size+1 потому что TCOLORS 2 раза
				} catch (Exception e) {
					pgConnection.rollback();
					h2Connection.rollback();
					throw e;
				}
	}

	protected ImportTable filterTable(Connection h2Connection, Connection pgConnection, ImportTable tbl) throws Exception {
		if (tbl == null)
			return tbl;

		// склад остатков
		if ("STOCK.TCONSIST_REMAINS".equalsIgnoreCase(tbl.toString()) && !isLoadConsistRemains)
			return null;

		// цены
		if (!isLoadPrices) {
			if ("CONTRACTORS.TSUPPLIER_PRICES".equalsIgnoreCase(tbl.toString())
					|| "SETTINGS.TPRICES".equalsIgnoreCase(tbl.toString())
					|| "CATALOG.TPAYMENTS".equalsIgnoreCase(tbl.toString())
					|| "CATALOG.TSERVICES".equalsIgnoreCase(tbl.toString()))
				return null;
			if ("CONTRACTORS.TCUSTOMERS".equalsIgnoreCase(tbl.toString()) || "CONTRACTORS.TSUPPLIERS".equalsIgnoreCase(tbl.toString()))
				tbl.removeColumn("TDISCOUNT_ID");
			if ("STOCK.TMATERIALS".equalsIgnoreCase(tbl.toString())) {
				tbl.removeColumn("PRICE_RUB");
				tbl.removeColumn("COST1");
				tbl.removeColumn("COST2");
				tbl.removeColumn("COST2$");
				tbl.removeColumn("COST3");
				tbl.removeColumn("COST3$");
				tbl.removeColumn("K2");
				tbl.removeColumn("BL_K2");
				tbl.removeColumn("K3");
				tbl.removeColumn("DEBIT_NORM");
			}
			if ("STOCK.TPROPERTIES".equalsIgnoreCase(tbl.toString()))
				tbl.removeColumn("BLANK_TSUPPLIER_ID");
		}

		// контрагенты
		if (!isLoadContractors) {
			if ("CONTRACTORS.TADDRESSES".equalsIgnoreCase(tbl.toString())
					|| "CONTRACTORS.TBANKS".equalsIgnoreCase(tbl.toString())
					|| "CONTRACTORS.TCATEGORIES".equalsIgnoreCase(tbl.toString())
					|| "CONTRACTORS.TCUSTOMERS".equalsIgnoreCase(tbl.toString())
					|| "CONTRACTORS.TDIRECTIONS".equalsIgnoreCase(tbl.toString())
					|| "CONTRACTORS.TDOCUMENTS".equalsIgnoreCase(tbl.toString())
					|| "CONTRACTORS.TNOTES".equalsIgnoreCase(tbl.toString())
					|| "CONTRACTORS.TSUPPLIER_PRICES".equalsIgnoreCase(tbl.toString())
					|| "CONTRACTORS.TSUPPLIERS".equalsIgnoreCase(tbl.toString()))
				return null;
			if ("ACCESSORIES.TALWAYS".equalsIgnoreCase(tbl.toString())
					|| "ACCESSORIES.TCALCULATED".equalsIgnoreCase(tbl.toString())
					|| "ACCESSORIES.TCELLS_ALWAYS".equalsIgnoreCase(tbl.toString())
					|| "ACCESSORIES.TCELLS_CALCULATED".equalsIgnoreCase(tbl.toString())
					|| "ACCESSORIES.TGROUPS".equalsIgnoreCase(tbl.toString())
					|| "ACCESSORIES.TRANGES_CALCULATED".equalsIgnoreCase(tbl.toString()))
				tbl.removeColumn("TRECIPIENT_ID");
			if ("STOCK.TPROPERTIES".equalsIgnoreCase(tbl.toString())) {
				tbl.removeColumn("BLANK_TSUPPLIER_ID");
				tbl.removeColumn("INCOMING_TRECIPIENT_ID");
				tbl.removeColumn("OUTGO_TRECIPIENT_ID");
				tbl.removeColumn("OUTGO_TRECYCLER_ID");
				tbl.removeColumn("OUTGO_TSUPPLIER_ID");
				tbl.removeColumn("WRITE_OFF_TRECIPIENT_ID");
				tbl.removeColumn("WRITE_OFF_TSUPPLIER_ID");
			}
		}

		if ("SETTINGS.TELEMENTS".equalsIgnoreCase(tbl.toString())) {
			try (PreparedStatement updStmt = h2Connection.prepareStatement("update SETTINGS.TELEMENTS set telement_type_id = ? where element_type_npp = ?");
					Statement selStmt = pgConnection.createStatement();
					ResultSet rs = selStmt.executeQuery("select id, npp from catalog.telement_types")) {
				while (rs.next()) {
					updStmt.setLong(1, rs.getLong("id"));
					updStmt.setLong(2, rs.getLong("npp"));
					updStmt.addBatch();
				}
				updStmt.executeBatch();
				h2Connection.commit();
			} catch (Exception e) {
				h2Connection.rollback();
				throw e;
			}
			tbl.removeColumn("ELEMENT_TYPE_NPP");
		}
		return tbl;
	}

	protected String getSrcFieldMapper(ImportTable table, String field) {
		return field;
	}

	protected String getDestFieldMapper(ImportTable table, String field) {
		return field;
	}

}