def hashHeight(height):
#query key images contained in a block height
cnx = mysql.connector.connect(**mysqlconfig)
cur = cnx.cursor()
s = "SELECT DISTINCT tx_idx, tx_hash, vin_idx, k_image FROM tx_vin_mixin WHERE header_height = %s"
s = s % (height)
cur.execute(s)
rows = cur.fetchall()
for row in rows:
tx_idx = row[0]
tx_hash = row[1]
vin_idx = row[2]
k_image = row[3]
kimageMixinCount = mixinOfKimageCount(k_image)
kimageMixinHash = hashData(mixinOfKimageStr(k_image))
#insert data
s1 = "INSERT INTO tx_vin_mixin_hash (header_height, tx_idx, tx_hash, vin_idx, k_image, vout_key_hash, vout_key_count) VALUES (%s, %s, %s, %s, %s, %s, %s)"
s1 = s1 % (height, tx_idx, QuotedStr(tx_hash), vin_idx,
QuotedStr(k_image), QuotedStr(kimageMixinHash),
kimageMixinCount)
print s1
cnx1 = mysql.connector.connect(**mysqlconfig)
cur1 = cnx1.cursor()
try:
cur1.execute(s)
cnx1.commit()
except:
cnx1.rollback()
#close the connection
cnx1.close()
#close the connection
cnx.close()
return height
def saveAHeaderToDB(height):
#get tx_hash
s = "SELECT tx_hash, tx_idx FROM header_tx WHERE header_height = %s ORDER BY tx_idx ASC"
s = s % (height)
cnx = mysql.connector.connect(**mysqlconfig)
cur = cnx.cursor()
cur.execute(s)
results = cur.fetchall()
#close the connection
cnx.close()
for result in results:
tx_hash = result[0]
tx_idx = result[1]
#print baseUrl + tx_hash
data = getContent(baseUrl + tx_hash)
#print data
jsonResponse = json.loads(data)
#print jsonResponse
inputs = jsonResponse["data"]["inputs"]
#print inputs
vin_idx = 0
for input in inputs:
amount = input["amount"]
k_image = input["key_image"]
#print "key image: ", k_image , "(amount: " , amount , ")"
mixins = input["mixins"]
mixin_idx = 0
for mixin in mixins:
public_key = mixin["public_key"]
block_no = mixin["block_no"]
#print "- public key: ", public_key, " (" , block_no , ")"
headerVinMixin = "INSERT INTO tx_vin_mixin (header_height, k_image, tx_hash, tx_idx, vin_idx, mixin_idx, vout_header_height, vout_key) VALUES "
dataVinMixin = ""
dataVinMixin = "(%s, %s, %s, %s, %s, %s, %s, %s)"
dataVinMixin = dataVinMixin % (
height, QuotedStr(k_image), QuotedStr(tx_hash), tx_idx,
vin_idx, mixin_idx, block_no, QuotedStr(public_key))
s = headerVinMixin + dataVinMixin
cnx = mysql.connector.connect(**mysqlconfig)
cur = cnx.cursor()
try:
cur.execute(s)
cnx.commit()
except:
cnx.rollback()
#close the connection
cnx.close()
#print s
time.sleep(0.01)
#increment
mixin_idx = mixin_idx + 1
#increment
vin_idx = vin_idx + 1
def saveTxDetailToDB(tx_hash):
#print baseUrl + tx_hash
data = getContent(baseUrl + tx_hash)
#print data
jsonResponse = json.loads(data)
#print jsonResponse
inputs = jsonResponse["data"]["inputs"]
height = jsonResponse["data"]["block_height"]
#set tx_idx = 0 because we do not have the data in the onion explorer
tx_idx = 0
#print inputs
vin_idx = 0
for input in inputs:
amount = input["amount"]
k_image = input["key_image"]
#print "key image: ", k_image , "(amount: " , amount , ")"
mixins = input["mixins"]
mixin_idx = 0
for mixin in mixins:
public_key = mixin["public_key"]
block_no = mixin["block_no"]
#print "- public key: ", public_key, " (" , block_no , ")"
headerVinMixin = "INSERT INTO tx_vin_mixin (header_height, k_image, tx_hash, tx_idx, vin_idx, mixin_idx, vout_header_height, vout_key) VALUES "
dataVinMixin = "(%s, %s, %s, %s, %s, %s, %s, %s)"
dataVinMixin = dataVinMixin % (
height, QuotedStr(k_image), QuotedStr(tx_hash), tx_idx,
vin_idx, mixin_idx, block_no, QuotedStr(public_key))
s = ""
s = headerVinMixin + dataVinMixin
cnx = mysql.connector.connect(**mysqlconfig)
cur = cnx.cursor()
try:
cur.execute(s)
cnx.commit()
except:
cnx.rollback()
#close the connection
cnx.close()
#print s
#time.sleep(0.01)
#increment
mixin_idx = mixin_idx + 1
#increment
vin_idx = vin_idx + 1
#
print "Transaction hash", tx_hash, " has been saved"
def saveDataTxVinMixinHash(height, tx_idx, tx_hash, vin_idx, k_image,
vout_key_hash, vout_key_count):
s = "INSERT INTO tx_vin_mixin_hash (header_height, tx_idx, tx_hash, vin_idx, k_image, vout_key_hash, vout_key_count) VALUES (%s, %s, %s, %s, %s, %s, %s)"
s = s % (height, tx_idx, QuotedStr(tx_hash), vin_idx, QuotedStr(k_image),
QuotedStr(vout_key_hash), vout_key_count)
#print s
cnx = mysql.connector.connect(**mysqlconfig)
cur = cnx.cursor()
try:
cur.execute(s)
cnx.commit()
except:
cnx.rollback()
#close the connection
cnx.close()
def saveTxDetailToDB(tx_hash):
#print baseUrl + tx_hash
data = getContent(baseUrl + tx_hash)
#print data
jsonResponse = json.loads(data)
#print jsonResponse
inputs = jsonResponse["data"]["inputs"]
outputs = jsonResponse["data"]["outputs"]
#set tx_idx = 0 because we do not have the data in the onion explorer
tx_idx = 0
#print inputs
vin_count = 0
for input in inputs:
#increment
vin_count = vin_count + 1
vout_count = 0
for output in outputs:
#increment
vout_count = vout_count + 1
s = "INSERT INTO tx_io (tx_hash, numinputs, numoutputs) VALUES (%s, %s, %s)"
s = s % (QuotedStr(tx_hash), vin_count, vout_count)
cnx = mysql.connector.connect(**mysqlconfig)
cur = cnx.cursor()
try:
cur.execute(s)
cnx.commit()
except:
cnx.rollback()
#close the connection
cnx.close()
def saveAHeaderToDB(height):
#get the data from the node
data = bitmonero.get_block_header_by_height(height)
if hasattr(data,"error"):
print "An Error in the header data has occured. Block height: " + str(height)
else:
#put the result to variables
jsonResponse = json.loads(data.to_JSON())
#print data.to_JSON()
hash = jsonResponse["hash"]
height = jsonResponse["height"]
difficulty = jsonResponse["difficulty"]
major_version = jsonResponse["major_version"]
minor_version = jsonResponse["minor_version"]
nonce = jsonResponse["nonce"]
prev_hash = jsonResponse["prev_hash"]
reward = jsonResponse["reward"]
timestamp = jsonResponse["timestamp"]
#save to database
cnx = mysql.connector.connect(**mysqlconfig)
cur = cnx.cursor()
s = "INSERT INTO header (hash, height, difficulty, major_version, minor_version, nonce, prev_hash, reward, timestamp) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)"
s = s % (QuotedStr(hash), height, difficulty, major_version, minor_version, nonce, QuotedStr(prev_hash), reward, timestamp)
#print s
try:
cur.execute(s)
cnx.commit()
except:
cnx.rollback()
#close the connection
cnx.close()
def mixinOfKimage(k_image):
cnx = mysql.connector.connect(**mysqlconfig)
cur = cnx.cursor()
s = "SELECT DISTINCT vout_key FROM tx_vin_mixin WHERE k_image = %s"
s = s % (QuotedStr(k_image))
#print s
cur.execute(s)
rows = cur.fetchall()
return rows
def saveTxDetailToDB():
firstBlock = dbGetLastTxHeight()
#lastBlock = dbGetFinalTxHeight()
#firstBlock = 71250
lastBlock = 100000
print "First block: ", str(firstBlock)
print "Last BLock: ", str(lastBlock)
#firstBlock = 1014786
#lastBlock = 1014787
height = firstBlock
while height < lastBlock:
#get tx_hash
s = "SELECT tx_hash, tx_idx FROM header_tx WHERE header_height = %s ORDER BY tx_idx ASC"
s = s % (height)
cnx = mysql.connector.connect(**mysqlconfig)
cur = cnx.cursor()
cur.execute(s)
results = cur.fetchall()
#close the connection
cnx.close()
headerVinMixin = "INSERT INTO tx_vin_mixin (header_height, k_image, tx_hash, tx_idx, vin_idx, mixin_idx, vout_header_height, vout_key) VALUES "
dataVinMixin = ""
for result in results:
tx_hash = result[0]
tx_idx = result[1]
#print baseUrl + tx_hash
data = getContent(baseUrl + tx_hash)
#print data
#print baseUrl + tx_hash
jsonResponse = json.loads(data)
#print jsonResponse
inputs = jsonResponse["data"]["inputs"]
#print inputs
vin_idx = 0
for input in inputs:
amount = input["amount"]
k_image = input["key_image"]
#print "key image: ", k_image , "(amount: " , amount , ")"
mixins = input["mixins"]
mixin_idx = 0
for mixin in mixins:
public_key = mixin["public_key"]
block_no = mixin["block_no"]
#print "- public key: ", public_key, " (" , block_no , ")"
singleVinMixin = "(%s, %s, %s, %s, %s, %s, %s, %s)"
singleVinMixin = singleVinMixin % (
height, QuotedStr(k_image), QuotedStr(tx_hash), tx_idx,
vin_idx, mixin_idx, block_no, QuotedStr(public_key))
if dataVinMixin == "":
dataVinMixin = singleVinMixin
else:
dataVinMixin = str(dataVinMixin) + "," + str(
singleVinMixin)
#increment
mixin_idx = mixin_idx + 1
#increment
vin_idx = vin_idx + 1
#compile the result
s = ""
if dataVinMixin <> "":
s = headerVinMixin + dataVinMixin
cnx = mysql.connector.connect(**mysqlconfig)
cur = cnx.cursor()
try:
cur.execute(s)
cnx.commit()
except:
cnx.rollback()
#close the connection
cnx.close()
#print s
time.sleep(0.01)
print "Block height " + str(height) + " processed."
#increment
height = height + 1
def saveAHeaderTxToDB(height):
#get the data from the node
data = bitmonero.get_block_by_height(height)
if hasattr(data, "error"):
print "An Error has occured when retrieving the tx data of block number " + str(
height)
else:
#put the result to variables
jsonResponse = json.loads(data.to_JSON())
#print data.to_JSON()
header_hash = jsonResponse["header"]["hash"]
header_height = jsonResponse["header"]["height"]
tx_hashes = jsonResponse["details"]["tx_hashes"]
#save the block reward
vouts = jsonResponse["details"]["miner_tx"]["vout"]
vout_idx = 0
#for temporary, set the vout_offset to 0 #TODO calculate the vout_offset based on the amount index
vout_offset = -1
for vout in vouts:
vout_amount = vout["amount"]
vout_key = vout["target"]["key"]
#save to database
cnx = mysql.connector.connect(**mysqlconfig)
cur = cnx.cursor()
s = "INSERT INTO tx_vout (header_height, tx_idx, amount, vout_key, tx_hash, vout_idx, vout_offset) VALUES (%s, %s,%s, %s, %s, %s, %s)"
s = s % (height, -1, vout_amount, QuotedStr(vout_key),
QuotedStr("reward"), vout_idx, vout_offset)
#print s
try:
cur.execute(s)
cnx.commit()
except:
cnx.rollback()
#close the connection
cnx.close()
#time.sleep(0.1)
#increase vin index
vout_idx = vout_idx + 1
#save the tx hashes
tx_idx = 0
for tx_hash in tx_hashes:
#save to database
cnx = mysql.connector.connect(**mysqlconfig)
cur = cnx.cursor()
s = "INSERT INTO header_tx (tx_hash, tx_idx, header_height, header_hash) VALUES (%s, %s, %s, %s)"
s = s % (QuotedStr(tx_hash), tx_idx, QuotedStr(header_height),
QuotedStr(header_hash))
try:
cur.execute(s)
cnx.commit()
except:
cnx.rollback()
#close the connection
cnx.close()
#time.sleep(0.1)
#print "Saving txid ", tx_hash, " to the database"
#index increment
tx_idx = tx_idx + 1
##########################save the tx detail here, trial#################################
xid = str(tx_hash)
data2 = bitmonero.get_transactions(xid)
jsonResponse2 = json.loads(data2.to_JSON())
#process the vin
#since we only have a single transaction
vins = jsonResponse2["found"][0]["vin"]
idx = 0
for vin in vins:
singleVin = ""
amount = vin["key"]["amount"]
k_image = vin["key"]["k_image"]
key_offsets = vin["key"]["key_offsets"]
#there are tx with large key offsets, we are removing this data for convenience
key_offsets = ""
s = "INSERT INTO tx_vin (header_height, tx_idx, tx_hash, amount, k_image, key_offsets, vin_idx) VALUES (%s, %s, %s, %s, %s, %s, %s)"
s = s % (header_height, tx_idx, QuotedStr(tx_hash), amount,
QuotedStr(k_image), QuotedStr(key_offsets), idx)
#print s
#send the vin to the database
cnx = mysql.connector.connect(**mysqlconfig)
cur = cnx.cursor()
try:
cur.execute(s)
cnx.commit()
except:
cnx.rollback()
#close the connection
cnx.close()
#time.sleep(0.1)
#increase vin index
idx = idx + 1
#process the vout
#since we only have a single transaction, therefore we only process the index 0
vouts = jsonResponse2["found"][0]["vout"]
idx = 0
#for temporary, set the vout_offset to 0
vout_offset = -1
for vout in vouts:
singleVout = ""
amount = vout["amount"]
vout_key = vout["target"]["key"]
s = "INSERT INTO tx_vout (header_height, tx_idx, amount, vout_key, tx_hash, vout_idx, vout_offset) VALUES (%s, %s,%s, %s, %s, %s, %s)"
s = s % (header_height, tx_idx, amount, QuotedStr(vout_key),
QuotedStr(tx_hash), idx, vout_offset)
#print s
cnx = mysql.connector.connect(**mysqlconfig)
cur = cnx.cursor()
try:
cur.execute(s)
cnx.commit()
except:
cnx.rollback()
#close the connection
cnx.close()
#time.sleep(0.1)
#increase vin index
idx = idx + 1
def saveATxToDB(header_height, tx_idx, tx_hash):
#get the data from the node
#tx_hash = SingleQuotedStr(tx_hash)
data = bitmonero.get_transactions(tx_hash)
#print data
jsonResponse = json.loads(data.to_JSON())
#print jsonResponse
#process the vin
#since we only have a single transaction
vins = jsonResponse["found"][0]["vin"]
#print vins
idx = 0
for vin in vins:
amount = vin["key"]["amount"]
k_image = vin["key"]["k_image"]
key_offsets = vin["key"]["key_offsets"]
#save to database
#this is where the error comes from#######################
cnx = mysql.connector.connect(**mysqlconfig)
cur = cnx.cursor()
s = "INSERT INTO tx_vin (header_height, tx_idx, tx_hash, amount, k_image, key_offsets, vin_idx) VALUES (%s, %s, %s, %s, %s, %s, %s)"
s = s % (header_height, tx_idx, QuotedStr(tx_hash), amount,
QuotedStr(k_image), QuotedStr(key_offsets), idx)
try:
cur.execute(s)
cnx.commit()
except:
cnx.rollback()
#close the connection
cnx.close()
#increase vin index
idx = idx + 1
#time.sleep(0.01)
#process the vout
#since we only have a single transaction, therefore we only process the index 0
vouts = jsonResponse["found"][0]["vout"]
idx = 0
#for temporary, set the vout_offset to 0
vout_offset = -1
for vout in vouts:
amount = vout["amount"]
vout_key = vout["target"]["key"]
#save to database
#this is where the error comes from#######################
cnx = mysql.connector.connect(**mysqlconfig)
cur = cnx.cursor()
s = "INSERT INTO tx_vout (header_height, tx_idx, amount, vout_key, tx_hash, vout_idx, vout_offset) VALUES (%s, %s,%s, %s, %s, %s, %s)"
s = s % (header_height, tx_idx, amount, QuotedStr(vout_key),
QuotedStr(tx_hash), idx, vout_offset)
try:
cur.execute(s)
cnx.commit()
except:
cnx.rollback()
#close the connection
cnx.close()
#increase vin index
idx = idx + 1
#time.sleep(0.01)
print "Saving txid ", tx_hash, " details to the database"