Overview

Context

The ledger database, which integrates a blockchain idea, records a user operation in two types of historical tables: a user history table and a global blockchain table. When a user creates a tamper-proof user table, the system automatically adds a hash column to the table to save the hash summary of each row of data. In blockchain mode, a user history table is created to record the change behavior of each data record in the user table. The user's modification to the tamper-proof user table will be all recorded in the global blockchain table. Because the history table can only be appended and cannot be modified, the records in the history table are concatenated to form the modification history of the tamper-proof user table.

The name and structure of the user history table are as follows:

**Table 1** Columns in the blockchain.<*schemaname*>_<*tablename*>_hist user history table
Column Name	Data Type	Description
rec_num	bigint	Sequence number of a row-level modification operation in the history table.
hash_ins	hash16	Hash value of the data row inserted by the INSERT or UPDATE operation.
hash_del	hash16	Hash value of the data row deleted by the DELETE or UPDATE operation.
pre_hash	hash32	Summary of the data in the history table of the current user.

**Table 2** Mapping between hash_ins and hash_del
-	hash_ins	hash_del
INSERT	(√) Hash value of the inserted row.	Empty.
DELETE	Empty.	(√) Hash value of the deleted row.
UPDATE	(√) Hash value of the newly inserted data.	(√) Hash value of the row before deletion.

Procedure

Create a schema in tamper-proof mode.
For example, create ledgernsp in tamper-proof mode.
1

gaussdb=# CREATE SCHEMA ledgernsp WITH BLOCKCHAIN;
To create a table in tamper-proof mode or change the common mode to the tamper-proof mode, set enable_ledger to on. The default value of enable_ledger is off.

Create a tamper-proof user table in tamper-proof mode.

For example, create a tamper-proof user table ledgernsp.usertable.

gaussdb=# CREATE TABLE ledgernsp.usertable(id int, name text);

Check the structure of the tamper-proof user table and the corresponding user history table.

gaussdb=# \d+ ledgernsp.usertable;
gaussdb=# \d+ blockchain.ledgernsp_usertable_hist;

The command output is as follows:

gaussdb=# \d+ ledgernsp.usertable;
                     Table "ledgernsp.usertable"
 Column     |  Type   | Modifiers | Storage  | Stats target | Description
--------+---------+-----------+----------+--------------+-------------
 id         | integer |           | plain    |              |
 name       | text    |           | extended |              |
 hash_69dd43   | hash16  |           | plain    |              |
Has OIDs: no
Options: orientation=row, compression=no
History table name: ledgernsp_usertable_hist

gaussdb=# \d+ blockchain.ledgernsp_usertable_hist;
             Table "blockchain.ledgernsp_usertable_hist"
  Column  |  Type  | Modifiers | Storage | Stats target | Description
----------+--------+-----------+---------+--------------+-------------
 rec_num  | bigint |           | plain   |              |
 hash_ins | hash16 |           | plain   |              |
 hash_del | hash16 |           | plain   |              |
 pre_hash | hash32 |           | plain   |              |
Indexes:
    "gs_hist_69dd43_index" PRIMARY KEY, btree (rec_num int4_ops) TABLESPACE pg_default
Has OIDs: no
Options: internal_mask=263

When a tamper-proof table is created, a system column named hash is automatically added. Therefore, the maximum number of columns in the tamper-proof table is 1599.

Modify the data in the tamper-proof user table.

For example, execute INSERT, UPDATE, or DELETE on the tamper-proof user table.

gaussdb=# INSERT INTO ledgernsp.usertable VALUES(1, 'alex'), (2, 'bob'), (3, 'peter');
INSERT 0 3
gaussdb=# SELECT *, hash_69dd43 FROM ledgernsp.usertable ORDER BY id;
 id | name  |       hash_69dd43
----+-------+------------------
  1 | alex  | 1f2e543c580cb8c5
  2 | bob   | 8fcd74a8a6a4b484
  3 | peter | f51b4b1b12d0354b
(3 rows)

gaussdb=# UPDATE ledgernsp.usertable SET name = 'bob2' WHERE id = 2;
UPDATE 1
gaussdb=# SELECT *, hash_69dd43 FROM ledgernsp.usertable ORDER BY id;
 id | name  |       hash_69dd43
----+-------+------------------
  1 | alex  | 1f2e543c580cb8c5
  2 | bob2  | 437761affbb7c605
  3 | peter | f51b4b1b12d0354b
(3 rows)

gaussdb=# DELETE FROM ledgernsp.usertable WHERE id = 3;
DELETE 1
gaussdb=# SELECT *, hash_69dd43 FROM ledgernsp.usertable ORDER BY id;
 id | name |       hash_69dd43
----+------+------------------
  1 | alex | 1f2e543c580cb8c5
  2 | bob2 | 437761affbb7c605
(2 rows)

Delete tables and schemas.

This step can be performed only after you have completed other operations (if any) on the ledger database.
```
gaussdb=# DROP TABLE ledgernsp.usertable;
DROP TABLE
gaussdb=# DROP SCHEMA ledgernsp;
DROP SCHEMA
```