public static DBRouter loadBasicRouter(
Map<Integer, Connection> cnxMap, String schemaName, Map<String, String> tableToColNames) {
System.out.println("loading router....");
DBRouter dbRouter = new DBRouter(new HashSet<Integer>(cnxMap.keySet()));
// dbRouter.addLookupTable(new BasicLookupTable("persons", "p_id", null));
if (DebugUtil.WIKIPEDIA_DATA_SET) {
try {
// create the range partition lookup on table name
StringRangeRouter srr = loadStringRangeRouter(cnxMap);
// create the dual lookup table for page.page_id and revision.rev_id
// LookupTable lt1 = loadLookupTable(schemaName, "revision", "rev_page", cnxMap);
LookupTable lt1 = loadLookupTable(schemaName, "page", "page_id", cnxMap);
// create the lookup table on text.old_id (can use revision.rev_text_id here)
LookupTable lt2 = loadLookupTable(schemaName, "revision", "rev_text_id", cnxMap);
lt2.setName("text");
dbRouter.addLookupTable(lt1);
dbRouter.addLookupTable(lt2);
dbRouter.addSecondaryLookup("page", srr);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return dbRouter;
} else if (DebugUtil.TPCE_DATA_SET) {
if (DebugUtil.TPCE_ROUTER_TYPE.equals(TpceRouterType.LOOKUP_TABLE)) {
try {
// read in the a_id -> partition table
ConcurrentHashMap<Long, Integer> a_id_map = loadConcurrentLongHashMap("a_by_aid", cnxMap);
ConcurrentLookupLong cl_a_id = new ConcurrentLookupLong(a_id_map);
// read in the b_id mapping
ConcurrentHashMap<Long, Integer> b_id_map = loadConcurrentLongHashMap("b_by_bid", cnxMap);
ConcurrentLookupLong cl_b_id = new ConcurrentLookupLong(b_id_map);
// read in the c_id mapping
ConcurrentHashMap<Long, Integer> c_id_map = loadConcurrentLongHashMap("c_by_cid", cnxMap);
ConcurrentLookupLong cl_c_id = new ConcurrentLookupLong(c_id_map);
// read in the c tax_id
ConcurrentHashMap<String, Integer> taxid_map =
loadConcurrentStringHashMap("c_by_taxid", cnxMap);
ConcurrentLookupString cl_taxid = new ConcurrentLookupString(taxid_map);
// read in the tid map
ConcurrentHashMap<Long, Integer> t_id_map = loadConcurrentLongHashMap("t_by_tid", cnxMap);
ConcurrentLookupLong cl_t_id = new ConcurrentLookupLong(t_id_map);
// create lt for trade
LookupMultiColumn ltTrade = new LookupMultiColumn("trade", dbRouter.allPartitionIds);
ltTrade.addLookupTable("t_id", cl_t_id);
ltTrade.addLookupTable("t_ca_id", cl_a_id);
// create lt for customer_account
LookupMultiColumn ltCustAcct =
new LookupMultiColumn("customer_account", dbRouter.allPartitionIds);
ltCustAcct.addLookupTable("ca_id", cl_a_id);
ltCustAcct.addLookupTable("ca_c_id", cl_c_id);
ltCustAcct.addLookupTable("ca_b_id", cl_b_id);
// create lt for customer
LookupMultiColumn ltCustomer =
new LookupMultiColumn("customer", dbRouter.allPartitionIds);
ltCustomer.addLookupTable("c_id", cl_c_id);
ltCustomer.addLookupTable("c_tax_id", cl_taxid);
// create lt for broker
LookupMultiColumn ltBroker = new LookupMultiColumn("broker", dbRouter.allPartitionIds);
ltBroker.addLookupTable("b_id", cl_b_id);
// add lt to the router
dbRouter.addLookupTable(ltBroker);
dbRouter.addLookupTable(ltCustomer);
dbRouter.addLookupTable(ltCustAcct);
dbRouter.addLookupTable(ltTrade);
return dbRouter;
} catch (SQLException e) {
// TODO Auto-generated catch block
throw new RuntimeException(e);
}
} else if (DebugUtil.TPCE_ROUTER_TYPE.equals(TpceRouterType.RANGE)) {
ConcurrentRangeLookup cl_broker = new ConcurrentRangeLookup(4300000001L, 4300000100L);
ConcurrentRangeLookup cl_cust = new ConcurrentRangeLookup(4300000001L, 4300010000L);
ConcurrentRangeLookup cl_account = new ConcurrentRangeLookup(43000000001L, 43000099992L);
// create lt for trade
LookupMultiColumn ltTrade = new LookupMultiColumn("trade", dbRouter.allPartitionIds);
ltTrade.addLookupTable("t_ca_id", cl_account);
// create lt for customer_account
LookupMultiColumn ltCustAcct =
new LookupMultiColumn("customer_account", dbRouter.allPartitionIds);
ltCustAcct.addLookupTable("ca_id", cl_account);
// create lt for customer
LookupMultiColumn ltCustomer = new LookupMultiColumn("customer", dbRouter.allPartitionIds);
ltCustomer.addLookupTable("c_id", cl_cust);
// create lt for broker
LookupMultiColumn ltBroker = new LookupMultiColumn("broker", dbRouter.allPartitionIds);
ltBroker.addLookupTable("b_id", cl_broker);
// add lt to the router
dbRouter.addLookupTable(ltBroker);
dbRouter.addLookupTable(ltCustomer);
dbRouter.addLookupTable(ltCustAcct);
dbRouter.addLookupTable(ltTrade);
return dbRouter;
} else if (DebugUtil.TPCE_ROUTER_TYPE.equals(TpceRouterType.HASH)) {
ConcurrentHashLookup cl_broker = new ConcurrentHashLookup();
ConcurrentHashLookup cl_cust = new ConcurrentHashLookup();
ConcurrentHashLookup cl_account = new ConcurrentHashLookup();
// create lt for trade
LookupMultiColumn ltTrade = new LookupMultiColumn("trade", dbRouter.allPartitionIds);
ltTrade.addLookupTable("t_ca_id", cl_account);
// create lt for customer_account
LookupMultiColumn ltCustAcct =
new LookupMultiColumn("customer_account", dbRouter.allPartitionIds);
ltCustAcct.addLookupTable("ca_id", cl_account);
// create lt for customer
LookupMultiColumn ltCustomer = new LookupMultiColumn("customer", dbRouter.allPartitionIds);
ltCustomer.addLookupTable("c_id", cl_cust);
// create lt for broker
LookupMultiColumn ltBroker = new LookupMultiColumn("broker", dbRouter.allPartitionIds);
ltBroker.addLookupTable("b_id", cl_broker);
// add lt to the router
dbRouter.addLookupTable(ltBroker);
dbRouter.addLookupTable(ltCustomer);
dbRouter.addLookupTable(ltCustAcct);
dbRouter.addLookupTable(ltTrade);
return dbRouter;
} else if (DebugUtil.TPCE_ROUTER_TYPE.equals(TpceRouterType.SINGLE_BACKEND)) {
OnePartitionTable opt_trade = new OnePartitionTable("trade", "", 1);
OnePartitionTable opt_cust_ac = new OnePartitionTable("customer_account", "", 1);
OnePartitionTable opt_cust = new OnePartitionTable("customer", "", 1);
OnePartitionTable opt_broker = new OnePartitionTable("broker", "", 1);
// add lt to the router
dbRouter.addLookupTable(opt_trade);
dbRouter.addLookupTable(opt_cust_ac);
dbRouter.addLookupTable(opt_cust);
dbRouter.addLookupTable(opt_broker);
return dbRouter;
} else {
throw new RuntimeException("other router types not supported for tpce");
}
} else if (DebugUtil.TWITTER_DATA_SET) {
if (DebugUtil.TWITTER_ROUTER_TYPE.equals(TwitterRouterType.SINGLE_BACKEND)) {
OnePartitionTable t_user = new OnePartitionTable("user", "uid", 1);
OnePartitionTable t_followers = new OnePartitionTable("followers", "f1", 1);
OnePartitionTable t_follows = new OnePartitionTable("follows", "f1", 1);
OnePartitionTable t_tweets = new OnePartitionTable("tweets", "uid", 1);
OnePartitionTable t_added_tweets = new OnePartitionTable("added_tweets", "uid", 1);
dbRouter.addLookupTable(t_tweets);
dbRouter.addLookupTable(t_user);
dbRouter.addLookupTable(t_followers);
dbRouter.addLookupTable(t_follows);
dbRouter.addLookupTable(t_added_tweets);
} else if (DebugUtil.TWITTER_ROUTER_TYPE.equals(TwitterRouterType.LOOKUP_TABLE)
|| DebugUtil.TWITTER_ROUTER_TYPE.equals(TwitterRouterType.ONE_HOP)) {
try {
OpenIntIntHashMap map = getIntegerMapUidToPartitionColt(cnxMap);
IntLookupTable lt_user = new IntLookupTable("user", "uid", map, dbRouter.allPartitionIds);
IntLookupTable lt_tweets = new IntLookupTable("tweets", "uid", lt_user);
IntLookupTable lt_follows = new IntLookupTable("follows", "f1", lt_user);
IntLookupTable lt_followers = new IntLookupTable("followers", "f1", lt_user);
IntLookupTable lt_added = new IntLookupTable("added_tweets", "uid", lt_user);
dbRouter.addLookupTable(lt_user);
dbRouter.addLookupTable(lt_tweets);
dbRouter.addLookupTable(lt_follows);
dbRouter.addLookupTable(lt_followers);
dbRouter.addLookupTable(lt_added);
} catch (SQLException e) {
// TODO Auto-generated catch block
throw new RuntimeException(e);
}
}
// also used for uber hash, but make sure lookup of 1 tweet has userid too
else if (DebugUtil.TWITTER_ROUTER_TYPE.equals(
TwitterRouterType
.REAL_HASH_UID)) { // also used for uber hash, but make sure tweet has userid too
HashPartitionLookup hp_user =
new HashPartitionLookup("user", "uid", dbRouter.allPartitionIds);
HashPartitionLookup hp_tweets =
new HashPartitionLookup("tweets", "uid", dbRouter.allPartitionIds);
HashPartitionLookup hp_follows =
new HashPartitionLookup("follows", "f1", dbRouter.allPartitionIds);
HashPartitionLookup hp_followers =
new HashPartitionLookup("followers", "f1", dbRouter.allPartitionIds);
HashPartitionLookup hp_added =
new HashPartitionLookup("added_tweets", "uid", dbRouter.allPartitionIds);
dbRouter.addLookupTable(hp_user);
dbRouter.addLookupTable(hp_tweets);
dbRouter.addLookupTable(hp_follows);
dbRouter.addLookupTable(hp_followers);
dbRouter.addLookupTable(hp_added);
} else if (DebugUtil.TWITTER_ROUTER_TYPE.equals(TwitterRouterType.REAL_HASH_TID)) {
HashPartitionLookup hp_user =
new HashPartitionLookup("user", "uid", dbRouter.allPartitionIds);
HashPartitionLookup hp_tweets =
new HashPartitionLookup("tweets", "id", dbRouter.allPartitionIds);
HashPartitionLookup hp_follows =
new HashPartitionLookup("follows", "f1", dbRouter.allPartitionIds);
HashPartitionLookup hp_followers =
new HashPartitionLookup("followers", "f1", dbRouter.allPartitionIds);
HashPartitionLookup hp_added =
new HashPartitionLookup("added_tweets", "uid", dbRouter.allPartitionIds);
dbRouter.addLookupTable(hp_user);
dbRouter.addLookupTable(hp_tweets);
dbRouter.addLookupTable(hp_follows);
dbRouter.addLookupTable(hp_followers);
dbRouter.addLookupTable(hp_added);
} else if (DebugUtil.TWITTER_ROUTER_TYPE.equals(TwitterRouterType.SINGLE_BACKEND)) {
assert dbRouter.allPartitionIds.size() == 1;
int partId = dbRouter.allPartitionIds.iterator().next();
OnePartitionTable o_user = new OnePartitionTable("user", "uid", partId);
OnePartitionTable o_tweets = new OnePartitionTable("tweets", "id", partId);
OnePartitionTable o_follows = new OnePartitionTable("follows", "f1", partId);
OnePartitionTable o_followers = new OnePartitionTable("followers", "f1", partId);
OnePartitionTable o_added = new OnePartitionTable("added_tweets", "uid", partId);
dbRouter.addLookupTable(o_user);
dbRouter.addLookupTable(o_tweets);
dbRouter.addLookupTable(o_follows);
dbRouter.addLookupTable(o_followers);
dbRouter.addLookupTable(o_added);
} else {
assert false : "no other twitter routers supported";
}
return dbRouter;
} else {
for (String tabName : tableToColNames.keySet()) {
try {
LookupTable lt =
RouterLoader.loadLookupTable(
schemaName, tabName, tableToColNames.get(tabName), cnxMap);
DebugUtil.print(lt.toString());
dbRouter.addLookupTable(lt);
} catch (SQLException e) {
// TODO Auto-generated catch block
throw new RuntimeException(e);
}
}
return dbRouter;
}
}
public static LookupTable loadLookupTable(
String schemaName, String tableName, String colName, Map<Integer, Connection> cnxMap)
throws SQLException {
System.out.println("loading table: " + tableName);
Connection conn = cnxMap.values().iterator().next();
Statement stmt = conn.createStatement();
DebugUtil.print("connection map: " + cnxMap);
if (cnxMap.keySet().size() == 1) {
return new OnePartitionTable(tableName, colName, cnxMap.keySet().iterator().next());
}
String typeSql =
"select COLUMN_TYPE from information_schema.`COLUMNS` where TABLE_SCHEMA='"
+ schemaName
+ "' AND TABLE_NAME='"
+ tableName
+ "' AND COLUMN_NAME='"
+ colName
+ "'";
ResultSet typeRes = stmt.executeQuery(typeSql);
typeRes.next();
String columnType = typeRes.getString("COLUMN_TYPE");
stmt.close();
// should support more types as enum
boolean isInt = false;
if (columnType.startsWith("int")) {
isInt = true;
}
String valSql = "select " + colName + " from " + tableName;
DebugUtil.print("creating mapping for table: " + tableName + " col: " + colName);
Map<Integer, Integer> mInt = new HashMap<Integer, Integer>();
// Map<Comparable, Integer> mInt = new HashMap<Comparable, Integer>();
Map<Comparable, Integer> m = new HashMap<Comparable, Integer>();
int cnt = 0;
for (Integer partition : cnxMap.keySet()) {
Statement stmt2 = cnxMap.get(partition).createStatement();
ResultSet partRes = stmt2.executeQuery(valSql);
if (isInt) {
while (partRes.next()) {
mInt.put(partRes.getInt(1), partition);
cnt++;
// DebugUtil.print("added: val " + partRes.getInt(1) +", by colname: " +
// partRes.getInt(colName)+ " to partition: " + partition);
}
}
// otherwise store as string... should support more later
else {
while (partRes.next()) {
m.put(partRes.getString(1), partition);
cnt++;
// DebugUtil.print("added: val " + partRes.getString(1) + " to partition: " +
// partition);
}
}
}
System.out.println(
"added " + cnt + " mappings to lookup table: " + tableName + " on column " + colName);
if (isInt) {
return new IntLookupTable(tableName, colName, mInt);
// return new BasicLookupTable(tableName, colName, columnType, mInt);
} else {
return new BasicLookupTable(tableName, colName, columnType, m);
}
}
/*
* used to create wikipedia table on title
*/
public static StringRangeRouter loadStringRangeRouter(Map<Integer, Connection> cnxMap)
throws SQLException {
DebugUtil.print("loading string range router");
String[] cutPoints;
int[] partitionIds;
if (DebugUtil.NUM_BACKENDS_FOR_WIKIPEDIA == 8) {
cutPoints = new String[7];
partitionIds = new int[8];
cutPoints[0] = "Bastion_(comics)";
cutPoints[1] = "Devil_May_Cry_2";
cutPoints[2] = "Gypsy_(comics)";
cutPoints[3] = "Lexington_class_aircraft_carrier_aircraft";
cutPoints[4] = "National_Football_League";
cutPoints[5] = "Requests_for_adminship/Biruitorul";
cutPoints[6] = "The_Impending_Crisis_of_the_South";
} else if (DebugUtil.NUM_BACKENDS_FOR_WIKIPEDIA == 4) {
cutPoints = new String[3];
partitionIds = new int[4];
cutPoints[0] = "Devil_May_Cry_2";
cutPoints[1] = "Lexington_class_aircraft_carrier_aircraft";
cutPoints[2] = "Requests_for_adminship/Biruitorul";
} else if (DebugUtil.NUM_BACKENDS_FOR_WIKIPEDIA == 2) {
cutPoints = new String[1];
partitionIds = new int[2];
cutPoints[0] = "Lexington_class_aircraft_carrier_aircraft";
} else if (DebugUtil.NUM_BACKENDS_FOR_WIKIPEDIA == 1) {
partitionIds = new int[1];
assert cnxMap.keySet().size() == 1;
partitionIds[0] = cnxMap.keySet().iterator().next();
return new StringRangeRouter(partitionIds);
} else {
throw new RuntimeException(
"NUM_BACKENDS_FOR_WIKIPEDIA=" + DebugUtil.NUM_BACKENDS_FOR_WIKIPEDIA + " not supported");
}
assert partitionIds.length == (cutPoints.length + 1);
String query_front = "SELECT page_title FROM `page` LIMIT 1;";
for (Integer partition : cnxMap.keySet()) {
Connection conn = cnxMap.get(partition);
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(query_front);
rs.first();
String title = rs.getString(1);
DebugUtil.print("partition: " + partition + " title: " + title);
boolean found = false;
for (int i = 0; i < cutPoints.length; i++) {
// if(title.compareToIgnoreCase(cutPoints[i]) < 0){
if (title.compareTo(cutPoints[i]) < 0) {
partitionIds[i] = partition;
found = true;
break;
}
}
if (found == false) {
partitionIds[cutPoints.length] = partition;
}
}
StringRangeRouter srr = new StringRangeRouter(cutPoints, partitionIds);
DebugUtil.print("" + srr);
return srr;
}
