根据数据库表的外键依赖顺序对表进行简单排序

lyb520320

浏览: 79672 次
性别:
来自: 广州

最近访客更多访客>>

cnlinkin

viotion

一代小辉

戒酒的李白xc

博主相关

博客

微博

相册

留言

关于我

文章分类

社区版块

存档分类

博客分类：

算法

数据库外键排序 db2 57016 外键依赖

最近在使用DB2的时候，老是出现57016的错误（由于表不活动，不能对表进行访问），特别是在数据导入和导出的时候，必定会出现，每次都要执行reorg一下。在reorg的过程中，如果该表有外键依赖，且外键表也不活动，就需要先对外键表进行reorg。

一次数据的导入，接近30%的表不能访问了，而且还有部分表的外键检查也被disable了，在reorg前，还要先恢复外键检查，非常麻烦。一个简单的办法是先删除所有外键，在reorg所有表之后再加上外键，但是这样还是可能在最后加外键的过程中表又不活动了。

所有我想对数据库的所有表按照外键依赖顺序进行排序，这样在reorg一张表之前，保证其依赖的外键表已经被reorg了。但是还是有一个问题，就是有些表有循环的外键依赖：对于这样的问题，只能通过先删除外键，reorg之后再加上。

下面是我用JAVA写的排序算法，包含了循环依赖检查(TableFK是一个非常简单的类，就不列出了)：

package test;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;

public class ReorgAllTable {

	private static final String SCHEMA = "TEST";

	private final static String DB_URL = "jdbc:db2://localhost:50000/SIMPLE";
	private final static String DB_DRIVER = "com.ibm.db2.jcc.DB2Driver";
	private final static String DB_USERNAME = "TEST";
	private final static String DB_PASSWORD = "TEST";

	private Map<String, List<TableFK>> tableFKListMap = new HashMap<String, List<TableFK>>();

	private Map<String, TableFK> tableFKMap = new HashMap<String, TableFK>();

	private List<String> sortedTableList = new ArrayList<String>();
	private Set<String> cycleFKSet = new HashSet<String>();

	static {
		try {
			Class.forName(DB_DRIVER);
		} catch (Exception e) {
			throw new ExceptionInInitializerError(e);
		}
	}

	/**
	 * @param args
	 * @throws Exception
	 */
	public static void main(String[] args) throws Exception {
		ReorgAllTable reorgAllTable=new ReorgAllTable();
		reorgAllTable.sortTableSet();
		String sql = reorgAllTable.createSortedSql();
		System.out.println(sql);
	}
	
	public void sortTableSet() throws Exception {
		fetchTableFkListMap();
		Set<String> tableSet=tableFKListMap.keySet();
		Iterator<String> tableSetIter = tableSet.iterator();
		while (tableSetIter.hasNext()) {
			String table = tableSetIter.next();
			sortTable(table, null);
		}
	}
	
	private void sortTable(String table, Set<String> pCycleCheckSet) {
		List<TableFK> tableFKList = getTableFKList(table);
		if (tableFKList == null || tableFKList.isEmpty()) {
			addTable(table);
			return;
		}
		for (TableFK tableFK : tableFKList) {
			String pTable = tableFK.PKTABLE_NAME;
			if (!sortedTableList.contains(pTable)) {
				// 循环依赖检查
				Set<String> cycleCheckSet = new HashSet<String>();
				if (pCycleCheckSet != null) {
					cycleCheckSet.addAll(pCycleCheckSet);
				}
				String fkName = tableFK.FK_NAME;
				if (!cycleCheckSet.contains(fkName)) {
					cycleCheckSet.add(fkName);
				} else {
					cycleFKSet.add(fkName);
					continue;
				}
				// 递归查找
				sortTable(pTable, cycleCheckSet);
				//
				cycleCheckSet.clear();
				cycleCheckSet = null;
			}
		}
		addTable(table);
	}
	
	private void addTable(String table) {
		if (!sortedTableList.contains(table)) {
			sortedTableList.add(table);
		}
	}

	private Map<String, List<TableFK>> fetchTableFkListMap() throws Exception {
		Connection connection = DriverManager.getConnection(DB_URL, DB_USERNAME, DB_PASSWORD);
		DatabaseMetaData metaData = connection.getMetaData();
		List<String> tableList = fetchTableList(connection,metaData);
		for (String table : tableList) {
			List<TableFK> tableFKList = new ArrayList<TableFK>();
			ResultSet importedKeys = metaData.getImportedKeys(null, SCHEMA, table);
			while (importedKeys.next()) {
				TableFK tableFK = new TableFK();
				tableFK.table = table;
				tableFK.PKTABLE_SCHEM = importedKeys.getString("PKTABLE_SCHEM");// 引用的外键表模式
				tableFK.PKTABLE_NAME = importedKeys.getString("PKTABLE_NAME");// 引用的外键表名
				tableFK.PKCOLUMN_NAME = importedKeys.getString("PKCOLUMN_NAME");// 引用的外键表主键列名
				tableFK.FKTABLE_SCHEM = importedKeys.getString("FKTABLE_SCHEM");// 主表模式
				tableFK.FKTABLE_NAME = importedKeys.getString("FKTABLE_NAME");// 主表名
				tableFK.FKCOLUMN_NAME = importedKeys.getString("FKCOLUMN_NAME");// 主表外键列名
				tableFK.FK_NAME = importedKeys.getString("FK_NAME");// 主表外键名
				tableFK.PK_NAME = importedKeys.getString("PK_NAME");// 引用的外键表主键名
				tableFKList.add(tableFK);
				tableFKMap.put(tableFK.FK_NAME, tableFK);
			}
			importedKeys.close();
			// System.out.println(tableFKList.toString());
			tableFKListMap.put(table, tableFKList);
		}
		connection.close();
		return tableFKListMap;
	}

	private List<String> fetchTableList(Connection connection, DatabaseMetaData metaData) throws SQLException {
		List<String> tableList = new ArrayList<String>();
		//也可以使用DatabaseMetaData.getTables(null,SCHEMA,"%",new String[]{"TABLE"})来获取所有表
		PreparedStatement prepareStatement = connection.prepareStatement("select name from sysibm.systables where type='T' and creator=?");
		prepareStatement.setString(1, SCHEMA);
		ResultSet rs = prepareStatement.executeQuery();
		while (rs.next()) {
			String table = rs.getString(1);
			tableList.add(table);
		}
		rs.close();
		prepareStatement.close();
		return tableList;
	}
	
	private TableFK getTableFK(String tableFKName) {
		return tableFKMap.get(tableFKName);
	}
	
	private List<TableFK> getTableFKList(String table) {
		return tableFKListMap.get(table);
	}

	public String createSortedSql() {
		StringBuilder sb = new StringBuilder();
		for (String fkName : cycleFKSet) {
			TableFK tableFK = getTableFK(fkName);
			String sqlDropFK = sqlDropFK(tableFK);
			sb.append(sqlDropFK);
		}
		for (String table : sortedTableList) {
			String sqlCheckFk = sqlCheckFk(table);
			sb.append(sqlCheckFk);
			String sqlReorg = sqlReorg(table);
			sb.append(sqlReorg);
		}
		for (String fkName : cycleFKSet) {
			TableFK tableFK = getTableFK(fkName);
			String sqlCreateFK = sqlCreateFK(tableFK);
			sb.append(sqlCreateFK);
		}
		return sb.toString();
	}

	private String sqlCheckFk(String table) {
		return "SET INTEGRITY FOR " + tableName(table) + " IMMEDIATE CHECKED;\n";
	}

	private String sqlReorg(String table) {
		return "REORG TABLE " + tableName(table) + ";\n";
	}

	private String sqlDropFK(TableFK tableFK) {
		return "ALTER TABLE " + tableName(tableFK) + " DROP FOREIGN KEY " + tableFK.FK_NAME + ";\n";
	}

	private String sqlCreateFK(TableFK tableFK) {
		return "ALTER TABLE " + tableName(tableFK) + " ADD CONSTRAINT " + tableFK.FK_NAME + " FOREIGN KEY(\"" + tableFK.FKCOLUMN_NAME
				+ "\")	REFERENCES " + tableName(tableFK.PKTABLE_NAME) + "(\"" + tableFK.PKCOLUMN_NAME
				+ "\")	ON DELETE RESTRICT	ON UPDATE RESTRICT	ENFORCED	ENABLE QUERY OPTIMIZATION;\n";
	}

	private String tableName(String table) {
		return SCHEMA + "." + table;
	}

	private String tableName(TableFK tableFK) {
		return SCHEMA + "." + tableFK.table;
	}
}

分享到：