go eth
https://github.com/DutchGraa/crackcoin
https://www.bigchaindb.com/developers/guide/
https://github.com/adamchinkc/blockchain_database
Blockchain Database API
Thin Database Architecture based on Blockchain technology
Background
When we talk about Blockchain, we always relate it to peer-to-peer network and think that data must be distributed across the network. It will raise concern from people and think that Blockchain would breach the confidentiality of the data.
Actually, the data architecture of Blockchain itself already provides a good solution for securing the data from unauthorized manipulation, given that the server is protected by sufficient controls, such as access control, network and system security control, and better to be in an internal network.
Therefore, I try to build an database based on the data architecture of Blockchain by using Python, Sqlite and RESTful API framework.
Objective
Accountability, Confidentially and Integrity
When the user create a transaction record, he encrypts the data with its private key and post the data to Blockchain Database API. Blockchain Database API will decrypt the data with the user’s public key. In this process, the user’s identity has been confirmed. It achieves the objective of accountability and confidentially.
In the next step, Blockchain Database API will calculate the hash value for the transaction with nonce, i.e. random string, and the previous hash. Blockchain Database API will insert the transaction, nonce and hash to the database.
To detect any unauthorized change, Blockchain Database API will re-calculate the hash value based on the information of the previous hash, transaction and nonce. If any change is made, the hash value will change and the API can be notified. Therefore, the integrity of the data will be ensured.
How to use?
Start the Hash API
python hash.py
Start the Nonce API
python nonce.py
Start the Main API
python main.py
Post the Journal Data to Main API http://127.0.0.1:8000/construct and Get back the Response with nonce and hash
data1 = { "journal_id": "JE000001", "entry_date" : "2016-11-06", "create_time" : "2016-11-06 18:00:00", "created_by": "Adam", "post_status": "P", "account_code" : "100000", "amount" : 16453.24, "dr_cr" : "C" }
Post the Response to Main API http://127.0.0.1:8000/insert
data1 = { "journal_id": "JE000001", "entry_date" : "2016-11-06", "create_time" : "2016-11-06 18:00:00", "created_by": "Adam", "post_status": "P", "account_code" : "100000", "amount" : 16453.24, "dr_cr" : "C", "nonue" : ".....", "hash" : "....." }
Verify your transaction by Get http://127.0.0.1:8000/verify?id=1
Limitation
Since it is in a centralized architecture, there is a possibility for the attacker, who obtains the administration right, to change the entire database by recalculating the hash value again. This can be safeguarded by the following solutions:
Clone the transaction to a secured log server
Back up the data incrementally (line by line transaction) rather than full backup
License
MIT License
Copyright (c) 2017 Adam K.C. Chin
image flow of eth
https://github.com/4c656554/BlockchainIllustrations/blob/master/Ethereum/EthBlockchain5.svg
blockkri1.py
from web3 import Web3
from organize import *
import time
import sqlite3 as sq3
#conn = sq3.connect("blockchain.db")
conn = sq3.connect("blockkri.db")
cur = conn.cursor()
# some SQL code, e.g. select first five entries of the table Quick
cur.execute("SELECT * FROM Quick LIMIT 5")
a = cur.fetchall() #list of tuples containing all elements of the row
print(a)
conn.close()
sql_helper.py
"""
Author: Aleksandra Sokolowska
for Validity Labs AG
"""
def create_database(cur):
""" create the schema for the database"""
quick = """
CREATE TABLE IF NOT EXISTS Quick (
balanceFrom TEXT,
balanceTo TEXT,
blockNumber INTEGER,
sender TEXT,
nonce INTEGER,
recipient TEXT,
txHash TEXT PRIMARY KEY,
value TEXT);"""
tx = """
CREATE TABLE IF NOT EXISTS TX (
blockNumber INTEGER,
contractAddress TEXT,
cumulativeGasUsed INTEGER,
gas INTEGER,
gasPrice INTEGER,
gasUsed INTEGER,
input TEXT,
logs TEXT,
logsBloom TEXT,
r TEXT,
s TEXT,
status INTEGER,
txHash TEXT PRIMARY KEY,
transactionIndex INTEGER,
v INTEGER);"""
blck = """
CREATE TABLE IF NOT EXISTS block (
blockGasUsed INTEGER,
blockHash TEXT,
blockLogsBloom TEXT,
blockNonce TEXT,
blockNumber INTEGER PRIMARY KEY,
difficulty TEXT,
extraData TEXT,
gasLimit INTEGER,
miner TEXT,
mixHash TEXT,
parentHash TEXT,
receiptsRoot TEXT,
sha3Uncles TEXT,
size INTEGER,
stateRoot TEXT,
timestamp INTEGER,
totalDifficulty TEXT,
transactions TEXT,
transactionsRoot TEXT,
uncles TEXT); """
cur.execute(quick)
cur.execute(blck)
cur.execute(tx)
def create_index(cur):
quick = "CREATE INDEX index_quick ON Quick(value, sender, recipient);"
tx = "CREATE INDEX index_TX ON TX(blockNumber, status);"
blck = "CREATE INDEX index_block ON block(timestamp);"
cur.execute(quick)
cur.execute(blck)
cur.execute(tx)
def update_database(cur, table_quick, table_tx, table_block):
""" write lists of dictionaries into the database"""
quick = """INSERT INTO Quick VALUES (:balanceFrom, :balanceTo, :blockNumber, :from, :nonce, :to, :txHash, :value); """
tx = """ INSERT INTO TX VALUES (:blockNumber, :contractAddress, :cumulativeGasUsed, :gas, :gasPrice, :gasUsed, :input, :logs, :logsBloom, :r, :s, :status, :txHash, :transactionIndex, :v); """
blck = """ INSERT INTO block VALUES (:blockGasUsed, :blockHash, :blockLogsBloom, :blockNonce, :blockNumber, :difficulty, :extraData, :gasLimit, :miner, :mixHash, :parentHash, :receiptsRoot, :sha3Uncles, :size, :stateRoot, :timestamp, :totalDifficulty, :transactions, :transactionsRoot, :uncles); """
cur.executemany(quick, table_quick)
cur.executemany(tx, table_tx)
cur.executemany(blck, table_block)
organize.py
"""
Author: Aleksandra Sokolowska
for Validity Labs AG
"""
def order_table_block(block, web3):
""" build a block table to be compatible with SQLite data types"""
block_data = web3.eth.getBlock(block)
block_table = dict(block_data)
#mapping keys to avoid name clashes
m = {'hash':'blockHash', 'gasUsed':'blockGasUsed',
'number':'blockNumber','logsBloom':'blockLogsBloom',
'nonce':'blockNonce'}
block_table = dict((m.get(k, k), v) for (k, v) in block_table.items())
#convert types to be SQLite-compatible
tostring = ['transactions', 'difficulty', 'totalDifficulty', 'uncles']
tohex = ['blockHash', 'blockLogsBloom', 'blockNonce', 'extraData', 'mixHash', 'parentHash', 'receiptsRoot', 'sha3Uncles', 'stateRoot', 'transactionsRoot']
for nn in block_table.keys():
if nn in tohex:
block_table[nn] = web3.toHex(block_table[nn])
elif nn in tostring:
block_table[nn] = str(block_table[nn])
return block_table, block_data
def order_table_quick(hashh, block, web3, balance=False):
""" build a Quick table to be compatible with SQLite data types; balance: do not read state; useful when the node still does full sync """
#open transaction data
tx_data = web3.eth.getTransaction(hashh)
#get addresses
addr_from = tx_data['from']
addr_to = tx_data['to']
#get balances of these addresses
if balance:
balance_from = web3.eth.getBalance(addr_from, block_identifier=block)
try:
balance_to = web3.eth.getBalance(addr_to, block_identifier=block)
except TypeError:
balance_to = -1
else:
balance_to = None
balance_from = None
#build a quick table
quick_table = {}
quick_keys = ['from', 'to', 'value', 'hash',
'nonce', 'blockNumber']
#convert types to be SQLite-compatible
for nn in quick_keys:
if nn=="hash":
quick_table["txHash"] = web3.toHex(tx_data[nn])
elif nn=="value":
quick_table["value"] = str(tx_data[nn])
else:
quick_table[nn] = tx_data[nn]
#add balances
quick_table['balanceTo'] = str(balance_to)
quick_table['balanceFrom'] = str(balance_from)
return quick_table, tx_data
def order_table_tx(tx_data,hashh, web3):
""" build a TX table to be compatible with SQLite data types"""
TX_table = dict(tx_data)
# pop data already in Quick
pop_tx_keys = ['from', 'to', 'value',
'nonce', 'blockHash', 'hash']
for nn in pop_tx_keys:
TX_table.pop(nn)
#add data from the receipt
receipt_data = web3.eth.getTransactionReceipt(hashh)
receipt_keys = ['contractAddress','cumulativeGasUsed',
'gasUsed', 'gasUsed', 'logs', 'logsBloom',
'status', 'transactionHash', 'transactionIndex']
for nn in receipt_keys:
try:
if nn=="logs":
TX_table[nn] = str(receipt_data[nn])
elif nn=="logsBloom":
TX_table[nn] = web3.toHex(receipt_data[nn])
elif nn=='transactionHash':
TX_table['txHash'] = receipt_data[nn]
else:
TX_table[nn] = receipt_data[nn]
except KeyError:
TX_table[nn] = -1
tohex = ['r', 's', 'txHash']
#conversion to strings
for nn in TX_table.keys():
if nn in tohex:
TX_table[nn] = web3.toHex(TX_table[nn])
return TX_table
def execute_sql(table_quick, table_tx, table_block):
import os
from sql_helper import create_database, update_database, create_index
import sqlite3 as sq3
db_name = 'blockkri.db'
db_is_new = not os.path.exists(db_name)
#connect to the database
conn = sq3.connect(db_name) # or use :memory: to put it in RAM
cur = conn.cursor()
if db_is_new:
print('Creating a new DB.')
create_database(cur)
create_index(cur)
update_database(cur,table_quick, table_tx, table_block)
else:
update_database(cur,table_quick, table_tx, table_block)
conn.commit()
conn.close()
database.py
"""
Author: Aleksandra Sokolowska
for Validity Labs AG
"""
from web3 import Web3
from organize import *
import time
#uncomment one of the options below
# 1. connection via Infura
#web3 = Web3(Web3.HTTPProvider("https://mainnet.infura.io/your-personal-number"))
web3 = Web3(Web3.HTTPProvider("https://ropsten.infura.io/v3/9a7580413f89437fa5b18e2b6db93ee2"))
#https://ropsten.infura.io/v3/9a7580413f89437fa5b18e2b6db93ee2
# 2. or connection via local node
#web3 = Web3(Web3.IPCProvider('/your-path-to/geth.ipc'))
# load a block.
Nblocks = 10000
output_every = 2
start_time = time.time()
try:
with open('lastblock.txt', 'r') as f:
start = int(f.read())+1
except FileNotFoundError:
start = 2000000
#define tables that will go to the SQLite database
table_quick = []
table_tx = []
table_block = []
count = 0
#loop over all blocks
for block in range(start, start+Nblocks):
block_table, block_data = order_table_block(block,web3)
#list of block data that will go to the DB
table_block.append(block_table)
#all transactions on the block
for hashh in block_data['transactions']:
#print(web3.toHex(hashh))
quick_table, tx_data = order_table_quick(hashh,block, web3)
table_quick.append(quick_table)
#list of tx data that will go to the DB
TX_table = order_table_tx(tx_data,hashh, web3)
table_tx.append(TX_table)
count = count + 1
#print(count)
#dump output every 2 blocks
if (count % output_every) == 0:
execute_sql(table_quick, table_tx, table_block)
#free up memory
del table_quick
del table_tx
del table_block
table_quick = []
table_tx = []
table_block = []
#update the current block number to a file
with open('lastblock.txt', 'w') as f:
f.write("%d" % block)
if (count % 10) == 0:
end = time.time()
with open('timeperXblocks.txt', 'a') as f:
f.write("%d %f \n" % (block, end-start_time))
if (count % 100) == 0:
print("100 new blocks completed.")