Pictured above: a new block is added to a venerable structure with the help of a framework

By: Tony Marston, Director, Research & Development, Geoprise Technologies Licensing

GM-X is an enterprise resources planning (ERP) system which was first created in January 2007 and went live at the first customer in June 2008. It was designed from the ground up as a Web application comprising a group of interconnected subsystems, and was developed using the RADICORE framework which I built in 2002 and published in 2006. GM-X has been continually enhanced and new subsystems added since its debut. Today it has 17 subsystems.

Each subsystem has its own database and its own collection of tasks (user transactions). Currently there are 389 tables, 759 relationships, and over 3,000 tasks.

We recently added a new GM-X subsystem for Blockchain management, and how this came about is the subject of this article.

In mid-October 2017, Nelson Nones, the Founder of Geoprise, contacted me and asked, “What do you know about blockchains?” “Nothing” I replied. He then described an idea which he’d been mulling over for some while. His idea began to seem more plausible after he discovered a product called MultiChain which is able to send large volumes of data between nodes on a blockchain, not just financial transactions as is the case with Bitcoin. This appeared to be possible using MultiChain data streams.

Over the next few weeks we discussed what MultiChain could do and how we could use it in our ERP application. We recognized straight away that using it in a stand-alone application would be a waste of time. Any blockchain requires a network of at least two or more nodes, so we needed a use case in which many organizations need to share information with one another. Nelson immediately brought up a use case which GM-X currently addresses for a major client of ours who procures products from a network of suppliers. This currently has the following steps:

1. The Buyer constructs a request for quotation (RFQ) and sends it to eligible Sellers.
2. Each Seller examines the RFQ and, if interested in the business, prepares a Quote which is then sent back to the Buyer.
3. The Buyer examines the various Quotes submitted by Sellers, chooses one of them, and then raises and sends a Purchase Order (PO) to the winning Seller.
4. The Seller fulfils the order and sends an advance shipment notice (ASN) to the Buyer.
5. The Buyer receives the shipment, matches it with the ASN and books it into inventory.

Long ago this communication was done by sending letters back and forth in the post (later, emails), but is most commonly handled nowadays by the Buyer setting up a Supplier Portal Web site which each Seller can log into to transact with that Buyer. But such a system has two disadvantages. First, on top of their own ERP system most Sellers need to learn and use many different Supplier Portals, one for each customer, which often requires double entry of transactions. Second, the Supplier Portal is available over the public Internet, and although each Seller has its own unique login it is well known that any Web site which is accessible by the public can be attacked and infiltrated by people with nefarious intentions.

So how can a blockchain solve these problems? For starters, it eliminates double entries by connecting the Sellers’ and Buyer’s ERP systems directly. This is conceptually similar to old-fashioned electronic data interchange (EDI) but much simpler, faster and less expensive to implement. It is also far more secure than EDI or the Internet:

• All the ERP systems remain safe and secure behind their respective organizations’ internal firewalls.
• Blockchains are not accessible via HTTP or HTTPS ports and are completely private.
• No one can join the blockchain at will. They must be specifically invited.
• When the invitation is accepted a new node is added to the network and its credentials are shared with all the other nodes.
• When a node publishes a message, it is first validated by all the other nodes before being either confirmed or rejected. Using MultiChain, the message can be a block of data up to 64 megabytes (MB) in size.
• Confirmed messages are then sent to every other node on the network, and then added to each node’s copy of the entire blockchain.

Once a message has been added to the blockchain it cannot be amended, so the copy of the blockchain database at each node holds a complete audit trail of every message sent. This makes the data permanent, tamper-proof and verifiable, and eliminates the reconciliation burden for trading partners.

There now follows a list of questions we asked ourselves in order to identify the various points which need to be addressed in our solution.

How could we utilize a blockchain in GM-X?

Although each blockchain maintains its own database of all messages which are sent, this is not a relational database which can be queried using SQL statements. We decided that each node would have its own local copy of GM-X and its relational database(s), and would only use the blockchain when data from that node needs to be sent to other nodes. Upon receipt of such a message each node would extract the message and copy its contents to its own copy of the GM-X database. Some people have suggested that the blockchain database may remove the need for a relational database, but the relational database maintains the current picture while the blockchain maintains a history of changes which result in the current picture.

How could we send data across the blockchain?

For data, each message can be either plain text or binary so the possible formats can be quite varied. Having used several different formats over my long career I chose eXtensible Markup Language (XML), mainly because I have used it extensively since 2002, both in the RADICORE framework and GM-X Web services integration. A series of functions already existed which could take data out of one or more records in one or more database tables, put that data into an XML document before it is sent, extract each table’s data from the XML document after it is received, and finally apply it to the database at the receiving end. Although XML documents can be quite large, as plain text files they can easily be zipped to make them smaller.

GM-X also manages documents and other multimedia content which is stored in files (for example, PDFs) instead of tables and records in a database. For these, the file contents will be placed in the XML document along with its metadata.

How could we ensure that only selected nodes could read a particular message?

As noted earlier, every node in a blockchain receives a copy of every message. This means that every node can read every message, which is a problem whenever a message is private and confidential. Fortunately, MultiChain provides a mechanism for protecting confidentiality which ensures that each message can only be read by authorized nodes. This uses two additional data streams, one (“public keys”) to hold each authorized node’s public key, and the other (“access”) holding the password for a particular message. There is a separate entry on the “access” stream for each combination of recipient node and message. Each of these entries is encrypted with the receiving node’s public key so it can only be decrypted with that node’s private key. Private keys are NEVER published on the blockchain (or anywhere else for that matter) so confidentiality is assured.

How could we know when to send data over the blockchain?

Changes to the database are made using a SQL query containing an insert, update or delete operation on a table. It is not necessary to send every database change over the blockchain network, so what’s needed is a signal which means, “When an insert, update or delete operation is performed on table X then send a blockchain message”.

Instead of inserting code into individual application components to identify when a message needs to be sent and what it should contain, we decided instead to build a separate Blockchain Trigger class which the RADICORE framework could automatically examine whenever an insert, update or delete operation is performed. This allows us to configure the signals which cause blockchain message to be sent, without amending any application code.

How could we know when to send data from related tables as well as the target table?

In some cases, like simple lookup tables, it is only necessary to send data from a single table across the blockchain network. Most of the time, however, the data is spread across several related tables whose contents are maintained by several other tasks, so we needed a mechanism to identify which additional tables to include in the blockchain message, even if they were not amended during the same task.

We decided to build a Related Tables class associated with the Blockchain Trigger class which identifies all the additional tables (if any) required for the data extract.

If related tables are involved, how could we delay the blockchain message until the collection of data is complete?

An entity such as an RFQ is not restricted to a single record on a single table. Each RFQ has one or more line items, and each of those line items has a foreign key to a record in the Product table which needs to be included as well. In situations like these the message shouldn’t be sent until the entire collection is complete.

In such cases the relevant “header” table in the GM-X application always has a status which starts off as “Pending” and can progress through any number of other states such as “Approved”, “Sent” and so on. We therefore decided to include a mechanism which postpones the sending of messages until the specified status is attained. This type of trigger isn’t fired with an insert or delete operation, only an update.

How could we identify the blockchain address for each node in the blockchain?

Each node in the blockchain is identified by a 38-character address. Each node is also a separate organization, so it has its own entry on the Organisation table of the GM-X Party database. Each party, in turn, can have any number of Contact Mechanisms of different types representing postal addresses, telephone numbers and email addresses, so we decided to add a new type which identifies blockchain addresses.

How could we identify which nodes should receive the key to unlock each message?

It is rare to want to send a message to every node on the blockchain, so for each message we needed a mechanism to identify the list of authorized recipients. Where GM-X already had a table identifying a list of valid parties, we amended it to hold each party’s blockchain address. Otherwise we had to create new tables for these data, and make the application aware of them, but this was relatively painless using the RADICORE framework.

How could we ensure that data which uses technical keys generated at one node does not conflict with similar data generated by other nodes?

Each node in the blockchain has its own copy of the GM-X application and database(s), with certain tables having primary keys which are supplied from running number sequences. These are known as technical or surrogate keys. As a result, it is possible for different nodes to send messages having conflicting primary keys. For example, when different Sellers independently generate the same quote number for different Quotes, how is it possible to avoid a collision at the Buyer’s application?

Geoprise was already well-versed in standards, such as GS1, for assigning globally unique identifiers to a wide variety of business objects. All these standards incorporate a globally unique company prefix, so we decided to change our primary keys from a simple 10 digit running sequence number to a 38-digit number which has two parts—a leading 12-digit company prefix followed by a 28-digit running sequence number. Why 38 digits? If we have to change the database then go straight to the biggest number available and so avoid having to increase it again at some point in the future.

After analyzing the various points which our solution needed to address, next came the tricky part—actually implementing a solution as Proof of Concept (PoC).

Starting late November 2017, I downloaded the MultiChain software and installed it on two workstations connected to the same local area network (LAN). I also downloaded the MultiChain Web Demo which is written in PHP, the same language as GM-X. This gave me valuable information on how to use the various JSON-RPC API calls which allow the GM-X application to communicate with MultiChain. My next step was to create two new PHP scripts—one acting as the client to send messages out, and another acting as the server to receive messages at the other end. I did this to test the code dealing with public keys and access streams which is responsible for encrypting and decrypting messages. This involved using the PHP OpenSSL and Bzip2 extensions. I used these two scripts to send an encrypted message from one workstation, then show the decrypted message on the other. At this point each message was constructed manually as I had yet to write the code to extract data from the GM-X database.

Having successfully completed the PoC, we decided to develop a working prototype using three GM-X servers. At least three servers were needed in order to demonstrate sending messages to all nodes as well as a selected node. The goals were to:

• Send changes to a single table;
• Send changes to a group of related tables; and
• Send a disk file along with data from a database table.

My first step was to build the Blockchain Trigger and Related Tables classes as well as the tasks to maintain them.

This is what the Update Blockchain Trigger task looks like:

In this example, a blockchain trigger has been set up for the order_header table with reference to the Alias order_header_p which is for purchase orders.

The Trigger Column and Trigger Value entries work together as follows:

• If both are empty the Blockchain Trigger will be fired on every insert, update and delete affecting Table Id;
• Otherwise it will only be fired on an update, when the Trigger Column contains the Trigger Value (‘Sent to Supplier,’ in this example).

The Distribution Table entry must be empty unless ‘Database table’ is selected from the Distribution Type dropdown, in which case the Distribution Table is either the Table Id, or a table which is directly related to the Table Id.

This is what the Update Related Table task looks like:

Here the term “Node Id” doesn’t refer to nodes (addresses) within a blockchain. It is the node in a tree structure where a senior node can have any number of child nodes in a hierarchical arrangement.

The validation rules for this table are made easy simply because of the way in which the application components are arranged, which in turn is determined by the RADICORE framework. Every GM-X subsystem has a Subsystem Directory as well as a database comprising a set of tables, each having a unique Table Id. Each table in the subsystem’s database has its own class. In some cases, a table class may have several subclasses, each having a unique Alias such as order_header_p for purchase orders (illustrated above), or order_header_s for sales orders.

When a table class or subclass is instantiated into an object it provides the following information:

• Field specifications for every column in that table.
• Options, if a dropdown list is specified for the field.
• Child (junior) tables, identifying the Table Id, Alias (if any) and the Column names which link the two tables.
• Parent (senior) tables, also identifying the Table Id, Alias (if any) and the Column names which link the two tables.

This means, for example, that the dropdown list of tables within a subsystem can be populated as soon as the user identifies a Subsystem Directory. Likewise, as soon as the user identifies a table, the dropdown list of Trigger Column values is populated (but may be left empty); and the dropdown list of Trigger Values is populated as soon as the user identifies a Trigger Column. If the ‘Database table’ option is selected from the Distribution Type dropdown, then the Distribution Table dropdown is populated only with tables that have a Contact Mechanism column representing blockchain addresses.

This design makes it possible to construct a hierarchy of related tables which are to be extracted similar to what is illustrated below:

Note here that a Party may be either an Organization or a Person, so only one of these child tables will actually contain data. This is an implementation of Silverston's Universal Data Model for parties.

This takes care of identifying which tables will be the subject of data extraction when a Blockchain Trigger is fired, but the next step is to actually fire the trigger when the relevant table is modified. Again, the RADICORE framework made this process surprisingly easy:

• Each table in the database has its own table class, which may have subclasses.
• The table class and any subclasses are specializations of a single abstract table class, and inherit methods from the abstract table class which perform the insert, update and delete operations.

It was therefore easy to amend the abstract table class to call a new function at the end of each of those methods to check if a trigger could be fired. The function is passed the following parameters:

• The method name (‘insert’, ‘update’ or ‘delete’).
• The current Table Id.
• The Alias name (if applicable).
• The current data for this table in an associative array.

Using this information, it is straightforward to perform a lookup on the Blockchain Trigger table to see if it contains a record which matches the current information. If it does then the following data is stored in a global variable:

• The Trigger Id.
• The method name.
• The primary key of the affected record.

Note that the trigger is not actually fired at this point as the operation is within a database transaction. If there is a failure down the line then the transaction is rolled back and the trigger never fired. It is only when the transaction is eventually committed that the trigger is actually fired.

blockchain_send.php

I had originally thought I could execute the necessary code as part of the online transaction but eventually decided to launch a background task instead as this avoids any delay in processing the online task. I therefore extended the code I had built earlier for the PoC, to read the Blockchain Trigger table as well as the application table that was just modified. As it is passed the primary key of the affected record it knows which one to read. It then starts building the XML document which will be published on the blockchain using the following format:

The method is either ‘insert’, ‘update’ or ‘delete’ and table is either the Table Id or its Alias (if applicable). Then follows an element for each column in that record with its value.

If that table has any related tables which should be included in the extract it will also contain one or both of the following elements:

Note that each parent_tables or child_tables element can contain any number of records from any number of tables. Each of those records can, in turn, contain their own collections of parent and child records. This is a fully recursive process, so it processes all the Related Tables until there is nothing left.

For delete operations, there is no need to supply any data other than the primary key of the trigger table. Any cascade deletes will automatically be performed when the message is processed at the receiving node.

If any record refers to a file then its contents are inserted into the XML document after being base64 encoded.

blockchain_receive.php

The last step was to execute a script at the receiving node in order to extract a message and process its contents against the local GM-X database. MultiChain provides a mechanism called walletnotify to run an external program upon receipt of a message. I created a new script called blockchain_receive.php by extending the code I had built earlier for the PoC.

This script does not need to use the Blockchain Trigger class because everything it needs is contained within the XML document. The root node identifies both the starting table and the method, so it is a simple process to navigate through the XML document and process each record one at a time. The table value identifies which table class needs to be instantiated into an object, then that table’s data is passed to that object using the specified method.

For insert and update operations, a special “insert or update” method is actually used which will check if a record with that primary key exists before performing the insert or update operation as appropriate.

Note also that if a record contains any entries in parent_tables then these will be processed before the record itself. Any child_tables entries will be processed afterwards before moving on to the next record in the XML document.

After completing the coding and testing I then uploaded everything to the GM-X servers, and just before New Year’s Eve I demonstrated the results to the Geoprise stakeholders. I showed that I could update a record on one node and propagate it to one or more of the other nodes. I further showed that I could send data from a collection of related tables, and also that I could send files as well as data. Whilst building the working prototype I also managed to change some technical primary keys from 10 to 38 digits to demonstrate that using a leading 12-digit company prefix followed by a 28-digit running sequence number was a practical proposition. (Note: as of February 2018, all sharable GM-X technical primary keys have been changed in this manner.) 

All the Geoprise stakeholders were impressed that I could accomplish so much is so little time, but this was only possible due to the design of the RADICORE framework which is centered around performing standard SQL operations on database tables. It was therefore a relatively simple process to identify when a particular operation was performed on a particular table, extract that data into an XML document, and then send that document to a blockchain. At the receiving end a script would extract the XML document from the blockchain database, then send each table’s data to the object which dealt with that table.

Because the sending of messages containing application data over the blockchain can be configured and managed within the framework and without having to modify any application components, this provides a simple yet flexible and powerful solution to a complex problem. As a follower of the KISS Principle this seemed the right thing to do.