Advertisement
AndrewHaxalot

bitcoin.sql

Jan 24th, 2014
456
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
SQL 7.96 KB | None | 0 0
  1. ---------------------------------------------------------------------------
  2. -- BASE TYPES
  3. ---------------------------------------------------------------------------
  4.  
  5. DROP DOMAIN IF EXISTS amount_type CASCADE;
  6. CREATE DOMAIN amount_type AS NUMERIC(16, 8) CHECK (VALUE < 21000000 AND VALUE >= 0);
  7. DROP DOMAIN IF EXISTS hash_type CASCADE;
  8. CREATE DOMAIN hash_type AS bytea;  -- 32*3 because "aa 0f ca ..."
  9.  
  10. CREATE OR REPLACE FUNCTION internal_to_sql(VALUE BIGINT) RETURNS amount_type AS $$
  11.     BEGIN
  12.         RETURN VALUE / CAST(100000000 AS NUMERIC(17, 8));
  13.     END;
  14. $$ LANGUAGE plpgsql;
  15.  
  16. CREATE OR REPLACE FUNCTION sql_to_internal(VALUE amount_type) RETURNS BIGINT AS $$
  17.     BEGIN
  18.         RETURN CAST(VALUE * 100000000 AS BIGINT);
  19.     END;
  20. $$ LANGUAGE plpgsql;
  21.  
  22. ---------------------------------------------------------------------------
  23. -- DIFFICULTY
  24. ---------------------------------------------------------------------------
  25.  
  26. -- 26959535291011309493156476344723991336010898738574164086137773096960
  27. -- That's the maximum target and the maximum difficulty
  28.  
  29. DROP DOMAIN IF EXISTS target_type CASCADE;
  30. CREATE DOMAIN target_type AS NUMERIC(68, 0) CHECK (VALUE <= 26959535291011309493156476344723991336010898738574164086137773096960 AND VALUE >= 0);
  31.  
  32. CREATE OR REPLACE FUNCTION extract_target(bits_head INT, bits_body INT) RETURNS target_type AS $$
  33.     BEGIN
  34.         RETURN bits_body * (2^(8*(CAST(bits_head AS target_type) - 3)));
  35.     END;
  36. $$ LANGUAGE plpgsql;
  37.  
  38. DROP DOMAIN IF EXISTS difficulty_type CASCADE;
  39. CREATE DOMAIN difficulty_type AS NUMERIC(76, 8) CHECK (VALUE <= 26959535291011309493156476344723991336010898738574164086137773096960 AND VALUE > 0);
  40.  
  41. CREATE OR REPLACE FUNCTION difficulty(bits_head INT, bits_body INT) RETURNS difficulty_type AS $$
  42.     BEGIN
  43.         RETURN extract_target(CAST(x'1d' AS INT), CAST(x'00ffff' AS INT)) / extract_target(bits_head, bits_body);
  44.     END;
  45. $$ LANGUAGE plpgsql;
  46.  
  47. ---------------------------------------------------------------------------
  48. -- BLOCKS
  49. ---------------------------------------------------------------------------
  50.  
  51. DROP TABLE IF EXISTS blocks;
  52. DROP SEQUENCE IF EXISTS blocks_block_id_sequence;
  53. DROP SEQUENCE IF EXISTS blocks_space_sequence;
  54. DROP TYPE IF EXISTS block_status_type;
  55.  
  56. CREATE SEQUENCE blocks_block_id_sequence;
  57. CREATE SEQUENCE blocks_space_sequence;
  58. -- Space 0 is always reserved for the main chain.
  59. -- Other spaces contain orphan chains
  60.  
  61. CREATE TABLE blocks (
  62.     block_id INT NOT NULL DEFAULT NEXTVAL('blocks_block_id_sequence') PRIMARY KEY,
  63.     block_hash hash_type NOT NULL UNIQUE,
  64.     SPACE INT NOT NULL,
  65.     depth INT NOT NULL,
  66.     span_left INT NOT NULL,
  67.     span_right INT NOT NULL,
  68.     version BIGINT NOT NULL,
  69.     prev_block_hash hash_type NOT NULL,
  70.     merkle hash_type NOT NULL,
  71.     when_created TIMESTAMP WITH TIME ZONE NOT NULL,
  72.     bits_head INT NOT NULL,
  73.     bits_body INT NOT NULL,
  74.     nonce BIGINT NOT NULL,
  75.     when_found TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
  76. );
  77.  
  78. -- Genesis block
  79. INSERT INTO blocks (
  80.     block_hash,
  81.     SPACE,
  82.     depth,
  83.     span_left,
  84.     span_right,
  85.     version,
  86.     prev_block_hash,
  87.     merkle,
  88.     when_created,
  89.     bits_head,
  90.     bits_body,
  91.     nonce
  92. ) VALUES (
  93.     decode('00 00 00 00 00 19 d6 68 9c 08 5a e1 65 83 1e 93 4f f7 63 ae 46 a2 a6 c1 72 b3 f1 b6 0a 8c e2 6f', 'hex'),
  94.     0,
  95.     0,
  96.     0,
  97.     0,
  98.     1,
  99.     decode('00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00', 'hex'),
  100.     decode('4a 5e 1e 4b aa b8 9f 3a 32 51 8a 88 c3 1b c8 7f 61 8f 76 67 3e 2c c7 7a b2 12 7b 7a fd ed a3 3b', 'hex'),
  101.     TO_TIMESTAMP(1231006505),
  102.     29,
  103.     65535,
  104.     2083236893
  105. );
  106.  
  107. CREATE INDEX ON blocks (block_hash);
  108. CREATE INDEX ON blocks (SPACE);
  109. CREATE INDEX ON blocks (depth);
  110.  
  111. DROP TABLE IF EXISTS chains;
  112. DROP VIEW IF EXISTS main_chain;
  113.  
  114. CREATE TABLE chains (
  115.     WORK difficulty_type NOT NULL,
  116.     chain_id INT NOT NULL,
  117.     depth INT NOT NULL
  118. );
  119.  
  120. INSERT INTO chains (
  121.     WORK,
  122.     chain_id,
  123.     depth
  124. ) SELECT
  125.     difficulty(bits_head, bits_body),
  126.     0,
  127.     0
  128. FROM blocks
  129. WHERE block_id=1;
  130.  
  131. CREATE VIEW main_chain AS
  132.     WITH main_chain_id AS (
  133.         SELECT
  134.             chain_id,
  135.             depth
  136.         FROM chains
  137.         ORDER BY WORK DESC
  138.         LIMIT 1
  139.     )
  140.     SELECT blocks.*
  141.     FROM blocks, main_chain_id
  142.     WHERE
  143.         SPACE=0
  144.         AND blocks.depth <= main_chain_id.depth
  145.         AND span_left >= chain_id
  146.         AND span_right <= chain_id;
  147.  
  148. ---------------------------------------------------------------------------
  149. -- INVENTORY QUEUE
  150. ---------------------------------------------------------------------------
  151.  
  152. DROP TABLE IF EXISTS inventory_requests;
  153. DROP SEQUENCE IF EXISTS inventory_requests_inventory_id_sequence;
  154. DROP TYPE IF EXISTS inventory_type;
  155.  
  156. CREATE SEQUENCE inventory_requests_inventory_id_sequence;
  157.  
  158. CREATE TYPE inventory_type AS ENUM ('block', 'transaction');
  159.  
  160. CREATE TABLE inventory_requests (
  161.     inventory_id INT NOT NULL DEFAULT NEXTVAL('inventory_requests_inventory_id_sequence') PRIMARY KEY,
  162.     TYPE inventory_type NOT NULL,
  163.     hash hash_type NOT NULL,
  164.     when_discovered TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
  165. );
  166.  
  167. ---------------------------------------------------------------------------
  168. -- TRANSACTIONS
  169. ---------------------------------------------------------------------------
  170.  
  171. DROP TABLE IF EXISTS transactions;
  172. DROP TABLE IF EXISTS transactions_parents;
  173. DROP TABLE IF EXISTS outputs;
  174. DROP TABLE IF EXISTS inputs;
  175. DROP TABLE IF EXISTS spends;
  176. DROP SEQUENCE IF EXISTS transactions_transaction_id_sequence;
  177. DROP SEQUENCE IF EXISTS outputs_output_id_sequence;
  178. DROP SEQUENCE IF EXISTS inputs_input_id_sequence;
  179.  
  180. -- Block 91842 contains the same coinbase as block 91812
  181. -- Same for blocks 91880 and 91722
  182. -- We use this function to create new txs.
  183. DROP FUNCTION IF EXISTS insert_transaction(hash_type, BIGINT, BIGINT, BOOL);
  184. CREATE OR REPLACE FUNCTION insert_transaction(tx_hash hash_type, tx_version BIGINT, tx_locktime BIGINT, tx_coinbase BOOL)
  185. RETURNS INT AS $$
  186. DECLARE
  187.  retval INT;
  188. BEGIN
  189.     INSERT INTO transactions(transaction_hash, version, locktime, coinbase)
  190.     VALUES (tx_hash, tx_version, tx_locktime, tx_coinbase)
  191.     RETURNING transaction_id INTO retval;
  192.  
  193.     RETURN retval;
  194.  
  195.     EXCEPTION
  196.         WHEN unique_violation THEN
  197.             RETURN 0;
  198. END;
  199. $$ LANGUAGE plpgsql;
  200.  
  201. CREATE SEQUENCE transactions_transaction_id_sequence;
  202. CREATE SEQUENCE outputs_output_id_sequence;
  203. CREATE SEQUENCE inputs_input_id_sequence;
  204.  
  205. CREATE TABLE transactions_parents (
  206.     transaction_id INT NOT NULL,
  207.     block_id INT NOT NULL,
  208.     index_in_block INT NOT NULL
  209. );
  210.  
  211. CREATE INDEX ON transactions_parents (transaction_id);
  212. CREATE INDEX ON transactions_parents (block_id);
  213.  
  214. CREATE TABLE transactions (
  215.     transaction_id INT NOT NULL DEFAULT NEXTVAL('transactions_transaction_id_sequence') PRIMARY KEY,
  216.     transaction_hash hash_type NOT NULL UNIQUE,
  217.     version BIGINT NOT NULL,
  218.     locktime BIGINT NOT NULL,
  219.     coinbase BOOL NOT NULL,
  220.     when_found TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
  221. );
  222.  
  223. DROP DOMAIN IF EXISTS script_type CASCADE;
  224. CREATE DOMAIN script_type AS bytea;
  225.  
  226. CREATE TABLE outputs (
  227.     output_id INT NOT NULL DEFAULT NEXTVAL('outputs_output_id_sequence') PRIMARY KEY,
  228.     transaction_id INT NOT NULL,
  229.     index_in_parent BIGINT NOT NULL,
  230.     script script_type NOT NULL,
  231.     VALUE amount_type NOT NULL
  232. );
  233.  
  234. CREATE INDEX ON outputs (transaction_id);
  235. CREATE INDEX ON outputs (script);
  236.  
  237. CREATE TABLE inputs (
  238.     input_id INT NOT NULL DEFAULT NEXTVAL('inputs_input_id_sequence') PRIMARY KEY,
  239.     transaction_id INT NOT NULL,
  240.     index_in_parent INT NOT NULL,
  241.     script script_type NOT NULL,
  242.     previous_output_hash hash_type NOT NULL,
  243.     previous_output_index BIGINT NOT NULL,
  244.     SEQUENCE BIGINT NOT NULL
  245. );
  246.  
  247. CREATE INDEX ON inputs (transaction_id);
  248. CREATE INDEX ON inputs (previous_output_hash, previous_output_index);
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement