Overview
Background
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 schema, 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 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 connected 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
Table 2 Mapping between hash_ins and hash_del
Procedure
Create a schema in tamper-proof mode.
For example, create ledgernsp in tamper-proof mode.
openGauss=# CREATE SCHEMA ledgernsp WITH BLOCKCHAIN;
Create a tamper-proof user table in tamper-proof mode.
For example, create a tamper-proof user table ledgernsp.usertable.
openGauss=# CREATE TABLE ledgernsp.usertable(id int, name text);
Check the structure of the tamper-proof user table and the corresponding user history table.
openGauss=# \d+ ledgernsp.usertable; openGauss=# \d+ blockchain.ledgernsp_usertable_hist;
The command output is as follows:
openGauss=# \d+ ledgernsp.usertable; Table "ledgernsp.usertable" Column | Type | Modifiers | Storage | Stats target | Description --------+---------+-----------+----------+--------------+------------- id | integer | | plain | | name | text | | extended | | hash | hash16 | | plain | | Has OIDs: no Distribute By: HASH(id) Location Nodes: ALL DATANODES Options: orientation=row, compression=no History table name: ledgernsp_usertable_hist openGauss=# \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_16388_index" PRIMARY KEY, btree (rec_num int4_ops) TABLESPACE pg_default Has OIDs: no Distribute By: HASH(rec_num) Location Nodes: ALL DATANODES Options: internal_mask=263
NOTE:
- Tamper-proof tables cannot be non-row-store tables, temporary tables, foreign tables, or unlogged tables. Non-row-store tables do not have the temper-proof attribute.
- When a temper-proof table is created, a system column named hash is automatically added. Therefore, the maximum number of columns in the temper-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.
openGauss=# INSERT INTO ledgernsp.usertable VALUES(1, 'alex'), (2, 'bob'), (3, 'peter'); INSERT 0 3 openGauss=# SELECT *, hash FROM ledgernsp.usertable ORDER BY id; id | name | hash ----+-------+------------------ 1 | alex | 1f2e543c580cb8c5 2 | bob | 8fcd74a8a6a4b484 3 | peter | f51b4b1b12d0354b (3 rows) openGauss=# UPDATE ledgernsp.usertable SET name = 'bob2' WHERE id = 2; UPDATE 1 openGauss=# SELECT *, hash FROM ledgernsp.usertable ORDER BY id; id | name | hash ----+-------+------------------ 1 | alex | 1f2e543c580cb8c5 2 | bob2 | 437761affbb7c605 3 | peter | f51b4b1b12d0354b (3 rows) openGauss=# SELECT *, hash FROM ledgernsp.usertable ORDER BY id; id | name | hash ----+-------+------------------ 1 | alex | 1f2e543c580cb8c5 2 | bob2 | 437761affbb7c605 3 | peter | f51b4b1b12d0354b (3 rows) openGauss=# DELETE FROM ledgernsp.usertable WHERE id = 3; DELETE 1 openGauss=# SELECT *, hash FROM ledgernsp.usertable ORDER BY id; id | name | hash ----+------+------------------ 1 | alex | 1f2e543c580cb8c5 2 | bob2 | 437761affbb7c605 (2 rows)