go eth

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?

1. Start the Hash API

python hash.py

1. Start the Nonce API

python nonce.py

1. Start the Main API

python main.py

1. 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" }

1. Post the Response to Main API http://127.0.0.1:8000/insert

1. 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

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.")

Page updated

Google Sites

Report abuse