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!
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:
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:
We start with a previoushash value of 0 since this is the first record, and the following result is given:
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:
So again we run the package:
and the following result is given:
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:
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:
Thanks for reading and please contact me on firstname.lastname@example.org or comment for more discussion.