How to create a Blockchain in Oracle

Oracle Blockchain

With the recent meteoric rise in Cryptocurrencies and Blockchain technology, many different languages are now being used to create a basic “bitcoin” like architecture.

In this example I demonstrate how to create a Blockchain in Oracle plsql with a basic table and package to “mine” and validate every “block”.

Please note that this has nothing to do with Blockchain Oracles which is a completely different thing!

Disclaimer
This code can NOT be used to mine actual bitcoins or any other Cryptocurrency. The code is neither optimised nor linked to any Blockchain.

The Oracle Blockchain Table

I initially created a simple table called “blockchain” which will contain each record or “block”.

In terms of this example, the table is the blockchain and the records are the blocks.

CREATE TABLE "BLOCKCHAIN"
 ( "ID" NUMBER,
 "PREVIOUSHASH" VARCHAR2(1000 BYTE),
 "TIMESTAMP" DATE,
 "TRANSACTION" VARCHAR2(1000 BYTE),
 "HASH" VARCHAR2(1000 BYTE),
 "NONCE" NUMBER,
 "VALID" VARCHAR2(1 BYTE)
 );

So the blockchain table has the following columns:

ID :- simply a number primary key
previoushash :- the previous hash value of the preceding block
timestamp :- the date and time of the block creation
transaction :- a text description of the monetary transaction
hash :- the sha256 hash of the current block
nonce :- I’ll explain this in more detail below, but it is basically an integer value that proves that the block has been “mined”
valid :- a Y or N flag showing that the blockchain is valid.

So when a new transaction is created it is inserted as a new row in the table.

The term “Blockchain” is actually very accurate since each row is “chained” or linked to the previous one. This means that the data is sequential, so whether there are 10 rows or 10 million rows the blockchain can be validated and, hence proved to be a true record. You will see this more clearly as we go through the example.

The Blockchain API

To manage the blockchain I’ve created a package with the following specification:

CREATE OR REPLACE PACKAGE blockchain_api as

procedure initialise;

procedure getLatestBlock;

procedure mineBlock;

procedure calculateHash;

function isChainValid
return boolean;

procedure isEntireChainValid;

end;
/

The procedures and functions in the package perform the following actions:

initialise :- This is the main controller for the blockchain and calls all other procedures and functions.

getLatestBlock :- This will get the latest block (record) and insert the values into global variables.

mineBlock :- The latest block is then mined.

calculateHash :- This procedure will calculate the sha256 hash value of the current block.

isChainValid :- Verifies if the current chain is valid in terms of the calculated nonce.

isEntireChainValid :- Verifies the entire blockchain table

Because I’m working in Oracle XE version 11.2, there is no native sha256 function. Therefore, I’ve had to use a third party package I found on github (https://github.com/CruiserX/sha256_plsql).

It’s probably worth noting at this point that the reason cryptocurrency is named as such is because of the sha256 hashing algorithm. Basically we are “hashing” a concatenation of the block’s previoushash, timestamp, transaction and nonce.

So what is a SHA256 hash?

For the uninitiated, it’s an encryption algorithm which can not be decrypted. So with a SHA256 (256 bit) hash you will always get a 64 character hexadecimal result. But what is really cool about hash functions is that even if you change one single character in the input string, you will get a completely different result.

This is great for cryptocurrency mining because essentially what we are mining for is a bunch of leading zeros in the sha256 result. Why? Because it’s rare and this is why mining is difficult and takes a lot of cpu time.

So this is where the concept of “difficulty” comes in. In the main package body there is a constant called c_difficulty which, for this example, is set to 2.

Proof of Work

This means that every time we hash the block with a different nonce value we are looking for 2 leading zeros.

In the procedure mineblock we start with a nonce value of 1 and continually increment it until the result gives the 2 leading zero’s as set in c_difficulty.

In the real bitcoin blockchain, the difficulty is set much higher.

So once we have mined our block, the values are written to the hash and nonce columns in the latest block in the blockchain table. This hashing of the block is what is called “Proof of Work“.

As a final check, the procedure isChainValid is invoked to verify that the block has been mined correctly and that the nonce value works. If the validation passes, the valid column is updated with a ‘Y’.

blockchain_api package body

CREATE OR REPLACE PACKAGE BODY blockchain_api AS

c_difficulty CONSTANT NUMBER := 2;
 gv_id blockchain.id%TYPE;
 gv_previoushash blockchain.previoushash%TYPE;
 gv_timestamp blockchain.timestamp%TYPE;
 gv_transaction blockchain.transaction%TYPE;
 gv_hash blockchain.hash%TYPE;
 gv_nonce blockchain.nonce%TYPE;

PROCEDURE initialise
 IS
 BEGIN

--get last block hash
 getlatestblock;
 
-- start mining - PROOF OF WORK
 mineblock;
 
 --validate block
 IF
 ischainvalid
 THEN
 UPDATE blockchain
 SET
 valid = 'Y'
 WHERE
 id = gv_id;

ELSE
 UPDATE blockchain
 SET
 valid = 'N'
 WHERE
 id = gv_id;

END IF;

END;


 PROCEDURE getlatestblock
 IS
 BEGIN
 FOR r IN (
 SELECT
 id,
 previoushash,
 timestamp,
 transaction
 FROM
 (
 SELECT
 *
 FROM
 blockchain
 ORDER BY
 id DESC
 )
 WHERE
 ROWNUM = 1
 ORDER BY
 ROWNUM DESC
 ) LOOP
 gv_id := r.id;
 gv_previoushash := r.previoushash;
 gv_timestamp := r.timestamp;
 gv_transaction := r.transaction;
 END LOOP;
 END;


 PROCEDURE mineblock IS
 l_leading_zeros VARCHAR2(100);
 BEGIN

--get leading zeros from difficulty
 SELECT
 rpad('0',c_difficulty,'0')
 INTO
 l_leading_zeros
 FROM
 dual;

--set inital value of nonce

gv_nonce := 1;
 calculatehash;

--cycle through hashes
 WHILE ( substr(gv_hash,1,c_difficulty) != l_leading_zeros ) LOOP
 gv_nonce := gv_nonce + 1; --increment the none by 1
 calculatehash;
 END LOOP;

--write new nonce to table, nonce has been found

UPDATE blockchain
 SET
 nonce = gv_nonce,
 hash = gv_hash
 WHERE
 id = gv_id;

END;

PROCEDURE calculatehash
 IS
 BEGIN
 gv_hash := sha256.encrypt(gv_previoushash
 || gv_timestamp
 || gv_transaction
 || gv_nonce);
 END;


 FUNCTION ischainvalid RETURN BOOLEAN IS
 l_result BOOLEAN := false;
 l_hash blockchain.hash%TYPE;
 l_mined_hash blockchain.hash%TYPE;
 BEGIN
 
 --get hash using current nonce
 SELECT
 sha256.encrypt(gv_previoushash
 || gv_timestamp
 || gv_transaction
 || gv_nonce)
 INTO
 l_hash
 FROM
 blockchain
 WHERE
 id = gv_id;

SELECT
 hash
 INTO
 l_mined_hash
 FROM
 blockchain
 WHERE
 id = gv_id;

IF
 ( l_hash = l_mined_hash )
 THEN
 l_result := true;
 END IF;
 RETURN l_result;
 END;

PROCEDURE isEntireChainValid IS

l_id blockchain.id%TYPE;
 l_previoushash blockchain.previoushash%TYPE;
 l_lag_hash blockchain.previoushash%TYPE;
 BEGIN
 FOR r IN (
 SELECT
 id,
 previoushash,
 LAG(hash,1) OVER(
 ORDER BY
 id
 ) lag_hash
 FROM
 blockchain
 ) LOOP
 l_id := r.id;
 l_previoushash := r.previoushash;
 l_lag_hash := r.lag_hash;
 IF
 l_previoushash != l_lag_hash
 THEN
 UPDATE blockchain
 SET
 valid = 'N'
 WHERE
 id = l_id;

ELSE
 UPDATE blockchain
 SET
 valid = 'Y'
 WHERE
 id = l_id;

END IF;

END LOOP;
 END;

END;
/

 

Finally, to validate the complete blockchain, I’ve added a procedure isEntireChainValid which will match the hash from the previous block (record) with the previoushash column on the current block.

This is another key feature of blockchains. When data is entering it is effectively “locked” into the chain and any tampering (hacking) will be immediately apparent when a validation check is performed. Further security is established when running a distributed peer-to-peer network where many 1000’s of mining servers have their own copy of the Blockchain. Therefore, if one node is found to be invalid it will be ignored by the rest of the network. It’s this decentralised architecture that gives blockchain it’s inherent security and stability.

The Blockchain in Action

So now that we have all the components of our blockchain, lets see it in action.

First we need to create a “Genesis” block. This is the first block which will contain the initial “dummy” transaction to start the chain off. This is referred to as the “Genesis” Block and is shown below:

genesis_block

 

 

 

 

 

At this point we need to mine the Genesis block to establish the first hash and nonce value. We do this using the package above as follows:

exec blockchain_api.initialise;

We start with a previoushash value of 0 since this is the first record, and the following result is given:

genesis_block_mined

So the initialise procedure goes to the most recent block (only one in this case) and mines it, giving a new hash and a nonce of 277. This means that it has looped through 277 nonce values before discovering 2 leading zero’s in the hash.

So let’s say that another block is created with a new transaction:

Note that the previous hash from the Genesis block is carried forward to the previoushash column on the new block.first_block

So again we run the package:

exec blockchain_api.initialise;

and the following result is given:

first_block_mined

We can see that the nonce was discovered after 133 attempts and the new hash written to the blockchain.

Following this protocol we can see that each new block is linked to the previous one and becomes an immutable record in our table. Provided that the blockchain table is validated in its entirety, any attempt to hack the system would be detected early on.

To complete the example let’s validate the whole blockchain:

exec blockchain_api.isentirechainvalid;

This procedure will compare the previoushash column with the hash in the previous block, thus “chaining” records together. If the values match then the valid column is updated with a ‘Y’. Otherwise it’s updated with a ‘N’.

The Future of Blockchain

Many projects are currently underway to explore possible uses of blockchain technology where a true record of data is required. Use Cases such as Voting systems and Public Registers are just two examples of how this architecture may benefit the public in the future.

 

The complete source code for this example can be found at github here:

https://github.com/drewette/plsql_crypto/

Thanks for reading and please contact me on info@orapro.co.uk or comment for more discussion.

Leave a Reply

Your email address will not be published. Required fields are marked *