Blockchain with Oracle and APEX
Thus the whole IT world talks about blockchains, the bitcoin price is as high as never (which underlying technology is a blockchain mechanism) and even Oracle has announced a new Blockchain Cloud Service at OOW, I looked into that topic and tried to create a simple blockchain by my own using the Oracle Database and of course APEX... Before we can start developing on a blockchain mechanism we have to investigate some time to get a basic understanding what a blockchain is and how it works all together. So lets get started with some terminology:
What is a Blockchain?
A blockchain is a distributed database with a list (chain) of records (blocks) linked and secured by digital fingerprints (crypto hashes)
[#<Block:0x1eed2a0
@index = 0,
@timestamp = 1637-09-15 20:52:38,
@data = "Genesis",
@previous_hash = "0",
@hash = "edbd4e11e69bc399a9ccd8faaea44fb27410fe8e3023bb9462450a0a9c4caa1b">,
#<Block:0x1eec9a0
@index = 1,
@timestamp = 1637-09-15 21:02:38,
@data = "Transaction Data...",
@previous_hash = "edbd4e11e69bc399a9ccd8faaea44fb27410fe8e3023bb9462450a0a9c4caa1b",
@hash = "eb8ecbf6d5870763ae246e37539d82e37052cb32f88bb8c59971f9978e437743">,
#<Block:0x1eec838
@index = 2,
@timestamp = 1637-09-15 21:12:38,
@data = "Transaction Data......",
@previous_hash = "eb8ecbf6d5870763ae246e37539d82e37052cb32f88bb8c59971f9978e437743",
@hash = "be50017ee4bbcb33844b3dc2b7c4e476d46569b5df5762d14ceba9355f0a85f4">,
...
What's a Hash? What's a Crypto(graphic) Hash Digest Checksum?
A hash e.g. eb8ecbf6d5870763ae246e37539d82e37052cb32f88bb8c59971f9978e437743 is a small digest checksum calculated with a one-way crypto(graphic) hash digest checksum function e.g. SHA256 (Secure Hash Algorithm 256 Bits) from the data
sha = Digest::SHA256.new
sha.update( @index + @timestamp + @data + @previous_hash )
A blockchain uses
the block index (e.g. 1,2,3,4, etc.)
the block timestamp (e.g. 10-OKT-2017 16:16:59,216566 +02:00)
the block data (e.g. Transaction Data...)
the hash from the previous block (e.g. edbd4e11e69bc399a9ccd8faaea44fb27410fe8e3023bb9462450a0a9c4caa1b)
the hash (e.g. be50017ee4bbcb33844b3dc2b7c4e476d46569b5df5762d14ceba9355f0a85f4): calculated by the hash digest checksum of (@index + @timestamp + @data + @previous_hash)
By design, blockchains are inherently resistant to modification of the data. You can not modify one block without modifying all other blocks in the chain too. If the blockchain is shared and distributed with others it is even more resistant to modification... Now, after knowing some basic concepts about blockchains the next step would be: How can we integrate such technology into the Oracle Database? And of course a basic blockchain is kind of simple to implement. Our blockchain would be nothing more than a simple table with the columns we already saw in the example above: index, timestamp, data, previous hash and hash. One table row would then be one block of the blockchain. So lets get finally into what we all like, PL/SQL stuff to explain it a bit better:
1. Our blockchain table
-- Create table
create table BLOCKCHAIN
(
bc_index number not null,
bc_timestamp timestamp with time zone not null,
bc_data clob not null,
bc_previous_hash varchar2(500) not null,
bc_hash varchar2(500) not null
);
-- Create/Recreate primary, unique and foreign key constraints
alter table BLOCKCHAIN
add constraint BLOCKCHAIN_PK primary key (BC_INDEX);
2. A sequence that increments our index column (Primary Key)
-- Create sequence
create sequence BLOCKCHAIN_SEQ
minvalue 1
maxvalue 9999999999999999999999999999
start with 1
increment by 1
nocache;
3. An before insert trigger that set our index column to a new incremented value on each insert
-- Create Before Insert Trigger
CREATE OR REPLACE TRIGGER blockchain_bi_trg
BEFORE INSERT ON blockchain
FOR EACH ROW
DECLARE
BEGIN
IF :new.bc_index IS NULL THEN
:new.bc_index := blockchain_seq.nextval;
END IF;
END;
After that, our blockchain is in place and waits to get filled with data. Before we can do this we will need some kind of API to do that. So lets create a PL/SQL package that manages some basic functionality to interact with our blockchain. That's what it should be able to do in the end:
calculate hashes
get a block
add a block
check blockchain health (valid or not)
get complete blockchain (starting with specified index)
And guess what? The Oracle Database has all features to do this! Thus we need cryptographic functionality (hashing) we will use DBMS_CRYPTO for this, so we will need a grant to our schema:
-- Grant to DBMS_CRYPTO as SYS
GRANT EXECUTE ON sys.dbms_crypto TO blockchain_schema;
If you´re not able or allowed to grant this package, then there´s an alternative open source PL/SQL utility for that: OOS Utils Now we can start with the PL/SQL package, here are only some code snippets to get a understanding, the whole source code with an APEX demo app is available on GitHub: https://github.com/Dani3lSun/oracle-blockchain
Get a block (basically get an table row record)
FUNCTION get_block(p_bc_index IN blockchain.bc_index%TYPE)
RETURN blockchain%ROWTYPE IS
--
l_blockchain_row blockchain%ROWTYPE;
--
BEGIN
--
BEGIN
SELECT *
INTO l_blockchain_row
FROM blockchain
WHERE blockchain.bc_index = p_bc_index;
EXCEPTION
WHEN no_data_found THEN
l_blockchain_row.bc_index := 0;
l_blockchain_row.bc_timestamp := to_timestamp('01-01-1970 00:00:00',
'DD-MM-YYYY HH24:MI:SS');
l_blockchain_row.bc_previous_hash := 0;
l_blockchain_row.bc_hash := 0;
END;
--
RETURN l_blockchain_row;
--
END get_block;
Calculate a SHA256 hash of a block
FUNCTION calculate_hash(p_bc_index IN blockchain.bc_index%TYPE,
p_bc_timestamp IN blockchain.bc_timestamp%TYPE,
p_bc_data IN blockchain.bc_data%TYPE,
p_hash_algorithm IN PLS_INTEGER := dbms_crypto.hash_sh256)
RETURN VARCHAR2 IS
--
l_prev_blockchain_row blockchain%ROWTYPE;
l_prev_hash VARCHAR2(500);
l_hash VARCHAR2(500);
l_hash_src CLOB;
--
BEGIN
--
l_prev_blockchain_row := blockchain_pkg.get_block(p_bc_index => blockchain_pkg.get_previous_block_index(p_current_index => p_bc_index));
l_prev_hash := l_prev_blockchain_row.bc_hash;
--
l_hash_src := p_bc_index ||
to_char(p_bc_timestamp,
'DD.MM.RRRR HH24:MI:SSXFF TZH:TZM',
'nls_numeric_characters = ''. ''') || p_bc_data ||
l_prev_hash;
--
l_hash := dbms_crypto.hash(l_hash_src,
p_hash_algorithm);
--
RETURN l_hash;
--
END calculate_hash;
Add a new block to our blockchain
FUNCTION add_block(p_bc_timestamp IN blockchain.bc_timestamp%TYPE := systimestamp,
p_bc_data IN blockchain.bc_data%TYPE)
RETURN blockchain.bc_index%TYPE IS
--
PRAGMA AUTONOMOUS_TRANSACTION;
--
l_prev_blockchain_row blockchain%ROWTYPE;
l_prev_hash VARCHAR2(500);
l_hash VARCHAR2(500);
l_bc_index blockchain.bc_index%TYPE;
--
BEGIN
--
LOCK TABLE blockchain IN EXCLUSIVE MODE;
--
l_bc_index := blockchain_seq.nextval;
l_prev_blockchain_row := blockchain_pkg.get_latest_block;
l_prev_hash := l_prev_blockchain_row.bc_hash;
l_hash := blockchain_pkg.calculate_hash(p_bc_index => l_bc_index,
p_bc_timestamp => p_bc_timestamp,
p_bc_data => p_bc_data);
--
INSERT INTO blockchain
(bc_index,
bc_timestamp,
bc_data,
bc_previous_hash,
bc_hash)
VALUES
(l_bc_index,
p_bc_timestamp,
p_bc_data,
l_prev_hash,
l_hash);
--
COMMIT;
--
RETURN l_bc_index;
--
END add_block;
Check health of blockchain (valid or not)
FUNCTION is_blockchain_valid RETURN BOOLEAN IS
--
l_current_block blockchain%ROWTYPE;
l_prev_block blockchain%ROWTYPE;
l_current_hash VARCHAR2(500);
--
CURSOR l_cur_blockchain IS
SELECT blockchain.bc_index
FROM blockchain
ORDER BY blockchain.bc_index;
--
BEGIN
--
FOR l_rec_blockchain IN l_cur_blockchain LOOP
l_current_block := blockchain_pkg.get_block(p_bc_index => l_rec_blockchain.bc_index);
l_prev_block := blockchain_pkg.get_block(p_bc_index => blockchain_pkg.get_previous_block_index(p_current_index => l_rec_blockchain.bc_index));
l_current_hash := blockchain_pkg.get_block_hash(p_blockchain_block => l_current_block);
--
IF l_current_block.bc_hash != l_current_hash THEN
RETURN FALSE;
END IF;
--
IF l_current_block.bc_previous_hash != l_prev_block.bc_hash THEN
RETURN FALSE;
END IF;
--
END LOOP;
--
RETURN TRUE;
--
END is_blockchain_valid;
But nothings better than see it in action, here´s a APEX live demo: https://orclapex.io/ords/f?p=BC As mentioned above, an export of this demo is available on GitHub...
Hope this blog post is useful for some people, as I do think that this technology is very powerful and enables many use cases to save data in an secure and unmodifiable way, e.g transactional data or bookings etc... Happy coding! :)