What's the best way to store unspent transaction?

Question

AFAIK, To send a raw transaction, which include both input and output. And What if we run it on the local node, we can simply fetch the unspent list by run bitcoin-cli listunspent, but If I just want to create a raw transaction on the client side, I believe the only way it store all the transaction to my local server. and before store it, I should do the following operations:

• 1.get raw transaction (getrawtransaction)
• 2.parser raw transaction (decoderawtransaction)
• 3.get the value of amount, and transaction id
• 4.put all together insert it into the mysql database

The table struct as :

mysql> desc t_tx;
+---------------+---------------+------+-----+---------------------+-------------------------------+
| Field         | Type          | Null | Key | Default             | Extra                         |
+---------------+---------------+------+-----+---------------------+-------------------------------+
| txid          | char(64)      | YES  |     | NULL                |                               |
| vout          | int(11)       | YES  |     | NULL                |                               |
| address       | char(35)      | NO   | PRI | NULL                |                               |
| redeemScript  | char(44)      | YES  |     | NULL                |                               |
| scriptPubKey  | char(46)      | YES  |     | NULL                |                               |
| amount        | decimal(16,8) | YES  |     | NULL                |                               |
| confirmations | bigint(20)    | YES  |     | NULL                |                               |
| spendable     | char(8)       | YES  |     | NULL                |                               |
| solvable      | char(8)       | YES  |     | NULL                |                               |
| safe          | char(8)       | YES  |     | NULL                |                               |
| blockhash     | char(64)      | YES  |     | NULL                |                               |
| created       | timestamp     | NO   |     | current_timestamp() | on update current_timestamp() |
+---------------+---------------+------+-----+---------------------+-------------------------------+

The table look so redundancy, I guess there are many columns I can remove from my table.

Look the output as below, If your guys can get a comment to each field at the end will be a really good for everyone, as I do at the end of txid.

[frank@localhost bin]$ ./bitcoin-cli decoderawtransaction 0200000001dbceae939bd37f2fe9dff0fca2aeaf44ec4987a34d59d58012982f49248d78fa0000000049483045022100f49a4e30e3baa5f00119f20e12a9bc932eea0814f7841ce0dc0ac17808d17e6e02207a50138fd23e7ac951fec96ff9869ecfbd495504889163a6d351ca07d2385de801feffffff02d8b068590000000017a914f393140f96b131c20ab063f025694dcade19e21e8700ca9a3b0000000017a914fc5d7d9af26be5f5656ac88023d1ff1683212dcc8737010000
{
  "txid": "172b99d21c02430fa16a70883d10090fde39c087ca82ebdf5a1b2567b115272e", // transaction id
  "hash": "172b99d21c02430fa16a70883d10090fde39c087ca82ebdf5a1b2567b115272e",
  "version": 2,
  "size": 188,
  "vsize": 188,
  "locktime": 311,
  "vin": [
    {
      "txid": "fa788d24492f981280d5594da38749ec44afaea2fcf0dfe92f7fd39b93aecedb",
      "vout": 0,
      "scriptSig": {
        "asm": "3045022100f49a4e30e3baa5f00119f20e12a9bc932eea0814f7841ce0dc0ac17808d17e6e02207a50138fd23e7ac951fec96ff9869ecfbd495504889163a6d351ca07d2385de8[ALL]",
        "hex": "483045022100f49a4e30e3baa5f00119f20e12a9bc932eea0814f7841ce0dc0ac17808d17e6e02207a50138fd23e7ac951fec96ff9869ecfbd495504889163a6d351ca07d2385de801"
      },
      "sequence": 4294967294
    }
  ],
  "vout": [
    {
      "value": 15.00033240,
      "n": 0,
      "scriptPubKey": {
        "asm": "OP_HASH160 f393140f96b131c20ab063f025694dcade19e21e OP_EQUAL",
        "hex": "a914f393140f96b131c20ab063f025694dcade19e21e87",
        "reqSigs": 1,
        "type": "scripthash",
        "addresses": [
          "2NFT8P5jxEGhSMY1xGqpADyPztK3pmh5pWb"
        ]
      }
    },
    {
      "value": 10.00000000,
      "n": 1,
      "scriptPubKey": {
        "asm": "OP_HASH160 fc5d7d9af26be5f5656ac88023d1ff1683212dcc OP_EQUAL",
        "hex": "a914fc5d7d9af26be5f5656ac88023d1ff1683212dcc87",
        "reqSigs": 1,
        "type": "scripthash",
        "addresses": [
          "2NGFcGUAUd9KyMpCJhqVZPfJ2mTi1rLvTtU"
        ]
      }
    }
  ]
}

As we know, the createrawtransaction function require an input and an output. but both input and output are array type, which means you can put more than one unspend transaction as input, and more than one dest address with amount into the output. but if I just want to create the raw transaction and sign it at the client side (mobile app), I need the node server support with api, which can call with an address and response all the unspent back!

I have tried all the command from bitcoin-cli, but non of them are useful at the scenes. so I want to subscribe the node information by zeroMQ, and then parser the txid by (getrawtransaction & decoderawtransaction), and then store all the related information into the mysql. But I don't know how to make this happend, because I have a little be confuse with the output from decoderawtransaction, and one can explain each field means? Especially, the vout inside the vin.

BTW, if you in think my solution is bad, please correct me, I am really newbie at the bitcoin!

Answer 1

To create a new transaction (with createrawtransaction) you will require a previous unspent vout - i.e. a UTXO, which is the result of the listunspent command. This will be the input of the transaction. The output of the transaction will be created on the fly, based on the amount and destination address. A transaction fee is likely necessary, and is calculated as tx fee = (sum of inputs) - (sum of outputs). Note that miners discriminate transactions based on the fee/vbyte, so a larger transaction (byte-size, not btc spent) needs a higher fee to be competitive.

The only thing you will need to store in your database is the vouts, indexed with the address or script hash. On each new block you will have to update those vouts, removing the ones that have been spent and add new unspent ones.