Redesign accounts ledger

[karmacoma]

Belongs to: #449

Task List:

Schema Changes

• Create new table accounts for store of immutable data about accounts, should contains following fields:
  "transaction_id" VARCHAR(20) NOT NULL REFERENCES transactions(id) ON DELETE CASCADE - That reference transaction that created account, so when that transaction is deleted due to rollback - account will be also deleted
  "public_key_transaction_id" VARCHAR(20) DEFAULT NULL REFERENCES transactions(transaction_id) ON DELETE SET NULL - That references transaction that created public_key
"public_key" BYTEA DEFAULT NULL
"address" VARCHAR(22) NOT NULL UNIQUE PRIMARY KEY - Here we have some issue because real address can be max 21 chars, but we already have 22 chars addresses - to research
  "balance" BIGINT DEFAULT 0

  • Add proper indexes

• Remove old tables: mem_accounts, mem_accounts2*

  • Replace mem_accounts2delegates with delegates table
  • Replace mem_accounts2multisignatures with multisignature_master table
  • Split column mem_accounts2multisignatures.keysgroup to be multisignature_member table, with relations to multisignature_master.public_key using master_public_key

• Create new view, accounts_list to provide abstraction for GET requests

• Add public_key field to following tables: second_signature, votes, multisignature_master multisignature_member, dapps, intransfer, outtransfer - so we can do easy joins with them on public_key, ensure proper foreign keys (also delegates table need to be reviewed)

• Propagate accounts table from blockchain data

  • Migrate balances from transactions
    • Migrate rewards/fees from rounds_rewards

Trigger changes or additions

• Write trigger that will set public_key to NULL when public_key_transaction_id is set to NULL
• Write triggers on transactions table that will maintain state of accounts table fields - AFTER INSERT
  • ALL: Check sender - update public_key field if it's NULL, update balance -(fee+amount)
  • TRANSFER - Check recipient - create account if not exists and update balance +amount
  • SIGNATURE - Update second_public_key if it's NULL // skipped - we will try to use existing signature table
• Write triggers on transactions table that will maintain state of accounts table in case of rollback - BEFORE DELETE or maybe AFTER DELETE (to research)
  • ALL: Check sender, update balance +(fee+amount), scan transactions table if
  • TRANSFER - Check recipient - update balance -amount

Code Changes

• Refactor existing application logic to support new table and relations
  • Alter all dbSave functions to be aware of new schema
  • API endpoints and related list functions1
  • Dealing with confirmed
    • Create account by transaction insert (Covered in triggers)
  • Remove apply*, undo*, etc. that affects balances only, transaction types should be preserved
• Refactor delegates_update_on_block and dependent functions such as delegates_voters_balance_update (rounds rewrite thing) to benefit from accounts table - should be easy and fast to generate changes from current round transactions and merge them with accounts.balance
• Remove undoUnconfirmed that use accounts.merge
• Remove apply functions that use accounts.merge
• Remove undo functions that use accounts.merge
• Remove applyUnconfirmed functions that use accounts.merge
• Remove account.set
• Remove account.merge
• Refactor setAccountAndGet
  • Accept transaction to pull properties from
  • Rename setAccountAndGet to getSender

Tests:

• Adjust tests

  • HTTP Post
  • HTTP Get
  • Unit tests

• Write new tests

  • Accounts sql
  • HTTP Post - Check 0 balance

[4miners]

Added task list to issue description.

[4miners]

#457 should be done first.