blockchain_mining.sql

# header for block 727155
DECLARE hash_prev_block STRING DEFAULT "00000000000000000004136135b2e0cd367b56ea6c0dd5b8f79964a4cd7d2718";
DECLARE merkle_root STRING DEFAULT "0d14fac91555d6337b10b2f20de231858fb5225f2ff685cd9b487c235d6e8307";
DECLARE header_datetime STRING DEFAULT "622defb9";
DECLARE bits STRING DEFAULT "170a3773";
DECLARE nonce STRING DEFAULT "c8f05860";
DECLARE nonce_dec INT64 DEFAULT 3371194460; # target is 3371194464
DECLARE version STRING DEFAULT "20002000";
DECLARE soft_forks STRING DEFAULT "0000000000000";
DECLARE version_roller INT64 DEFAULT 1;
DECLARE version_roller_bin STRING;
DECLARE version_bits STRING DEFAULT "001";

DECLARE header STRING;
DECLARE b_header BYTES;
DECLARE header_hash STRING;

DECLARE mantissa INT64;
DECLARE exponent INT64;
DECLARE target FLOAT64;

DECLARE max_iterations INT64 DEFAULT 48;
DECLARE iteration INT64 DEFAULT 0;

SET exponent = CAST(CONCAT("0x", SUBSTRING(bits, 0, 2)) AS INT64);
SET mantissa = CAST(CONCAT("0x", SUBSTRING(bits, 2, 4)) AS INT64);

SET target = mantissa * POWER(256, exponent);

main_loop: WHILE true DO

    SET version_roller_bin = SUBSTR(bqutil.fn.to_binary(CAST(version_roller AS INT64)), -16);
    SET version = FORMAT("%X", bqutil.fn.from_binary(CONCAT(version_bits, version_roller_bin, soft_forks)));

    WHILE true DO

        SET nonce = FORMAT("%X", nonce_dec);

        SET header = 
            TO_HEX(REVERSE(FROM_HEX(version))) || 
            TO_HEX(REVERSE(FROM_HEX(hash_prev_block))) || 
            TO_HEX(REVERSE(FROM_HEX(merkle_root))) || 
            TO_HEX(REVERSE(FROM_HEX(header_datetime))) || 
            TO_HEX(REVERSE(FROM_HEX(bits))) || 
            TO_HEX(REVERSE(FROM_HEX(nonce)));

        SET b_header = FROM_HEX(header);

        SET header_hash = TO_HEX(REVERSE(SHA256(SHA256(b_header))));

        IF CAST(CONCAT("0x", header_hash) AS FLOAT64) < target THEN
            SELECT nonce, iteration;
            BREAK main_loop;
        END IF;

        IF iteration >= max_iterations THEN
            SELECT "No nonce found after n iteraions", iteration;
            BREAK main_loop;
        END IF;

        SET nonce_dec = nonce_dec + 1;
        SET iteration = iteration + 1;

    END WHILE;

    SET version_roller = version_roller + 1;
    
END WHILE;