decred.org/dcrdex@v1.0.5/server/db/driver/pg/internal/accounts.go (about)

     1  package internal
     2  
     3  const (
     4  	// CreateFeeKeysTable creates the fee_keys table, which is a small table that
     5  	// is used as a persistent child key counter for master extended public key.
     6  	CreateFeeKeysTable = `CREATE TABLE IF NOT EXISTS %s (
     7  		key_hash BYTEA PRIMARY KEY,    -- UNIQUE INDEX
     8  		child INT8 DEFAULT 0
     9  		);`
    10  
    11  	// CreateAccountsTable creates the account table.
    12  	CreateAccountsTable = `CREATE TABLE IF NOT EXISTS %s (
    13  		account_id BYTEA PRIMARY KEY,  -- UNIQUE INDEX
    14  		pubkey BYTEA
    15  		);`
    16  
    17  	CreateBondsTableV0 = `CREATE TABLE IF NOT EXISTS %s (
    18  		version INT2,
    19  		bond_coin_id BYTEA,
    20  		asset_id INT4,
    21  		account_id BYTEA,
    22  		amount INT8, -- informative, strength is what matters
    23  		strength int4,
    24  		lock_time INT8,
    25  		PRIMARY KEY (bond_coin_id, asset_id)
    26  		);`
    27  	CreateBondsTable = CreateBondsTableV0
    28  
    29  	CreateBondsAcctIndexV0 = `CREATE INDEX IF NOT EXISTS %s ON %s (account_id);`
    30  	CreateBondsAcctIndex   = CreateBondsAcctIndexV0
    31  
    32  	CreateBondsLockTimeIndexV0 = `CREATE INDEX IF NOT EXISTS %s ON %s (lock_time);`
    33  	CreateBondsLockTimeIndex   = CreateBondsLockTimeIndexV0
    34  
    35  	CreateBondsCoinIDIndexV0 = `CREATE INDEX IF NOT EXISTS %s ON %s (bond_coin_id, asset_id);`
    36  	CreateBondsCoinIDIndex   = CreateBondsCoinIDIndexV0
    37  
    38  	AddBond = `INSERT INTO %s (version, bond_coin_id, asset_id, account_id, amount, strength, lock_time)
    39  		VALUES ($1, $2, $3, $4, $5, $6, $7);`
    40  
    41  	DeleteBond = `DELETE FROM %s WHERE bond_coin_id = $1 AND asset_id = $2;`
    42  
    43  	SelectActiveBondsForUser = `SELECT version, bond_coin_id, asset_id, amount, strength, lock_time FROM %s
    44  		WHERE account_id = $1 AND lock_time >= $2
    45  		ORDER BY lock_time;`
    46  
    47  	// InsertKeyIfMissing creates an entry for the specified key hash, if it
    48  	// doesn't already exist.
    49  	InsertKeyIfMissing = `INSERT INTO %s (key_hash)
    50  		VALUES ($1)
    51  		ON CONFLICT (key_hash) DO NOTHING
    52  		RETURNING child;`
    53  
    54  	CurrentKeyIndex = `SELECT child FROM %s WHERE key_hash = $1;`
    55  
    56  	SetKeyIndex = `UPDATE %s
    57  		SET child = $1
    58  		WHERE key_hash = $2;`
    59  
    60  	UpsertKeyIndex = `INSERT INTO %s (child, key_hash)
    61  		VALUES ($1, $2)
    62  		ON CONFLICT (key_hash) DO UPDATE
    63  		SET child = $1;`
    64  
    65  	// CloseAccount sets the broken_rule column for the account, which signifies
    66  	// that the account is closed.
    67  	CloseAccount = `UPDATE %s SET broken_rule = $1 WHERE account_id = $2;`
    68  
    69  	// SelectAccount gathers account details for the specified account ID.
    70  	SelectAccount = `SELECT pubkey
    71  		FROM %s
    72  		WHERE account_id = $1;`
    73  
    74  	// SelectAccountInfo retrieves all fields for an account.
    75  	SelectAccountInfo = `SELECT account_id, pubkey FROM %s
    76  		WHERE account_id = $1;`
    77  
    78  	CreateAccountForBond = `INSERT INTO %s (account_id, pubkey) VALUES ($1, $2);`
    79  
    80  	CreatePrepaidBondsTable = `CREATE TABLE IF NOT EXISTS %s (
    81  		coin_id BYTEA PRIMARY KEY,
    82  		version INT2 DEFAULT 0,
    83  		strength int4,
    84  		lock_time INT8
    85  	);`
    86  
    87  	SelectPrepaidBond = `SELECT strength, lock_time FROM %s WHERE coin_id = $1;`
    88  
    89  	DeletePrepaidBond = `DELETE FROM %s WHERE coin_id = $1;`
    90  
    91  	InsertPrepaidBond = `INSERT INTO %s (coin_id, strength, lock_time) VALUES ($1, $2, $3);`
    92  )