def create_txs_table(table_name):
create_txs_sql = """CREATE TABLE {} ( `id` int(11) NOT NULL AUTO_INCREMENT,
`source` char(42) NOT NULL, `target` char(42) NOT NULL, `amount` varchar(32) NOT NULL,
`tx` char(66) NOT NULL, `block_num` int(11) NOT NULL, `tx_seq` int(11) NOT NULL,
PRIMARY KEY (`id`), KEY `block_num_index` (`block_num`),
FULLTEXT `target_index` (`target`), FULLTEXT `source_index` (`source`),
FULLTEXT `tx_index` (`tx`) ) ENGINE=InnoDB""".format(
table_name)
exeSQL(create_txs_sql, True)
def initClusterFeaturesTable():
create_table_balance = ("CREATE TABLE {} ("
"id INT NOT NULL AUTO_INCREMENT,"
"address CHAR(42),"
"split_point_start INT NOT NULL, "
"split_point_stop INT NOT NULL, "
"in_num INT NOT NULL,"
"in_ethers FLOAT NOT NULL,"
"in_addrs_set_len INT NOT NULL,"
"in_dec_places INT NOT NULL,"
"out_num INT NOT NULL,"
"out_ethers FLOAT NOT NULL,"
"out_addrs_set_len INT NOT NULL,"
"out_dec_places INT NOT NULL,"
"init_balance FLOAT NOT NULL,"
"final_balance FLOAT NOT NULL,"
"PRIMARY KEY(id)) ; ".format(feature_table_name))
exeSQL(create_table_balance, True)
def query_account(addr):
query_account_sql = (
"SELECT * FROM {} WHERE address = '{}' limit 1").format(
table_name, addr)
query_account_sql_result = exeSQL(query_account_sql)
return query_account_sql_result
def drop_table():
drop_table_sql = """DROP TABLE account"""
exeSQL(drop_table_sql, True)
def create_account_table():
create_account_sql = """CREATE TABLE account ( `id` int(11) NOT NULL AUTO_INCREMENT,
`address` char(42) NOT NULL, `kind` enum('normal','sc','none') NOT NULL,
PRIMARY KEY (`id`)) ENGINE=InnoDB"""
exeSQL(create_account_sql, True)
def dropClusterFeatureTable():
drop_table_balance = ("DROP TABLE IF EXISTS {}".format(feature_table_name))
exeSQL(drop_table_balance, True)
def fetchAddressSet(account_type):
fetch_addrs_sql = ("SELECT {} FROM {}").format(account_type, action_table_name)
addrs = exeSQL(fetch_addrs_sql)
flat_addrs = [item for sublist in addrs for item in sublist]
return set((flat_addrs))
def insertFeaturesPerAddr(addr):
if (len(queryClusterFeatures(addr)) > 0):
return
fetch_txs_sql = ("SELECT * FROM {} "
"WHERE (source = '{}' OR target = '{}') ").format(action_table_name, addr, addr)
txs = exeSQL(fetch_txs_sql)
# Sort the txs
sorted_txs = sorted(txs, key=operator.itemgetter(6, 7, 8))
split_points = []
last_tx_type = 'out'
for index, tx in enumerate(sorted_txs):
if tx[2] == addr:
last_tx_type = 'out'
continue
else:
if last_tx_type == 'out':
split_points.append(index)
last_tx_type = 'in'
split_points.append(len(sorted_txs))
split_features = []
# print (split_points)
for i in range(len(split_points)-1):
sub_txs = sorted_txs[split_points[i]:split_points[i+1]]
in_num = 0
out_num = 0
in_ethers = 0
out_ethers = 0
in_addrs_set = set()
out_addrs_set = set()
in_ethers_decimal_places_dict = {}
out_ethers_decimal_places_dict = {}
for tx in sub_txs:
if tx[2] == addr:
out_num += 1
out_ethers += int(tx[4], 16) / 10**18
out_addrs_set.add(tx[3])
out_ethers_decimal_places = - decimal.Decimal(str(int(tx[4], 16) / 10**18)).as_tuple().exponent
if out_ethers_decimal_places in out_ethers_decimal_places_dict:
out_ethers_decimal_places_dict[out_ethers_decimal_places] += 1
else:
out_ethers_decimal_places_dict[out_ethers_decimal_places] = 1
else:
in_num += 1
in_ethers += int(tx[4], 16) / 10**18
in_addrs_set.add(tx[2])
in_ethers_decimal_places = - decimal.Decimal(str(int(tx[4], 16) / 10**18)).as_tuple().exponent
if in_ethers_decimal_places in in_ethers_decimal_places_dict:
in_ethers_decimal_places_dict[in_ethers_decimal_places] += 1
else:
in_ethers_decimal_places_dict[in_ethers_decimal_places] = 1
init_balance = checkBalance(addr, hex(sub_txs[0][6]-1))
final_balance = checkBalance(addr, hex(sub_txs[-1][6]))
max_freq_in_ethers_decimal_places = max(in_ethers_decimal_places_dict, key=in_ethers_decimal_places_dict.get) if len(in_ethers_decimal_places_dict)>0 else -1
max_freq_out_ethers_decimal_places = max(out_ethers_decimal_places_dict, key=out_ethers_decimal_places_dict.get) if len(out_ethers_decimal_places_dict)>0 else -1
fecture = [addr, split_points[i], split_points[i+1], in_num, in_ethers, len(in_addrs_set), max_freq_in_ethers_decimal_places,
out_num, out_ethers, len(out_addrs_set), max_freq_out_ethers_decimal_places,
init_balance, final_balance]
split_features.append(fecture)
# return split_features
# print (split_features)
insertClusterFeatures(split_features)
def queryClusterFeatures(addr):
query_feature_sql = ("SELECT * FROM {} WHERE address = '{}' limit 1").format(feature_table_name, addr)
features = exeSQL(query_feature_sql)
return features
