gbw-node/library/schema-node.sql [gbw-node

gbw-node/library/schema-node.sql

1	--- Gales Bitcoin Wallet: node (online component) schema
2	--- J. Welsh, December 2019
3	--- Dialect: SQLite (3.7.0 for WAL)
4
5	PRAGMA journal_mode=WAL;
6	BEGIN;
7
8	CREATE TABLE tx (
9	tx_id INTEGER PRIMARY KEY,
10	hash BLOB NOT NULL,
11	block_hash BLOB NOT NULL,
12	block_height INTEGER NOT NULL,
13	n INTEGER NOT NULL, -- position in block
14	comment TEXT,
15	size INTEGER NOT NULL,
16	fee INTEGER
17	);
18	CREATE UNIQUE INDEX i_tx_hash ON tx(hash);
19	CREATE UNIQUE INDEX i_tx_height_n ON tx(block_height, n);
20
21	CREATE TABLE output (
22	output_id INTEGER PRIMARY KEY,
23	tx_id INTEGER NOT NULL REFERENCES tx,
24	n INTEGER NOT NULL, -- position in output vector
25	address_id INTEGER NOT NULL REFERENCES address,
26	value INTEGER NOT NULL,
27	spent INTEGER REFERENCES input(input_id),
28	flags TEXT
29	);
30	CREATE UNIQUE INDEX i_output_txid_n ON output(tx_id, n);
31	CREATE INDEX i_output_addrid ON output(address_id);
32	CREATE UNIQUE INDEX i_output_spent ON output(spent);
33
34	CREATE TABLE input (
35	input_id INTEGER PRIMARY KEY,
36	tx_id INTEGER NOT NULL REFERENCES tx,
37	n INTEGER NOT NULL -- position in input vector
38	);
39	CREATE UNIQUE INDEX i_input_txid_n ON input(tx_id, n);
40
41	CREATE TABLE address (
42	address_id INTEGER PRIMARY KEY,
43	address BLOB NOT NULL
44	);
45	CREATE UNIQUE INDEX i_address_address ON address(address);
46
47	CREATE TABLE tag (
48	tag_id INTEGER PRIMARY KEY,
49	name TEXT NOT NULL
50	);
51	CREATE UNIQUE INDEX i_tag_name ON tag(name);
52
53	CREATE TABLE address_tag (
54	address_id INTEGER NOT NULL REFERENCES address,
55	tag_id INTEGER NOT NULL REFERENCES tag,
56	PRIMARY KEY (address_id, tag_id)
57	);
58	CREATE INDEX i_addrtag_tag ON address_tag(tag_id);
59
60	CREATE TABLE state (
61	scan_height INTEGER NOT NULL DEFAULT(-1)
62	);
63	INSERT INTO state DEFAULT VALUES;
64
65	COMMIT;