Reverse iterated index for latest per-account activity

eth-tx-prefix.cc

/*

	Build:
	g++ -std=c++11 -O -Wall -g -o eth-tx-prefix eth-tx-prefix.cc -lrocksdb

	Run:
	./eth-tx-prefix

	Clean:
	rm -rf eth-tx-prefix-db/

 */

#include <string>
#include <vector>
#include <map>
#include <stdlib.h>
#include <stdio.h>
#include <rocksdb/db.h>

using namespace std;

vector<string> rawdata = {
// ----ACCOUNT-DATE-------TIME---TXID--------------------------------------
	"0x111/2018/01/01/012200/21653edbb94b74071a3925267394f9ac419c292d",
	"0x111/2018/01/01/112234/6fa20f3b9249bee487b9b35fe6aed324a5b33e1d",
	"0x111/2018/01/02/112235/8f931e1414240217545feb29cfd51515ee653df2",
	"0x111/2017/12/22/231833/ecfea5128d3651a78df2088d60cec348ac5db104",
	"0x111/2017/12/22/231833/efa7fbc628d76f208f5ea2f5a5b04ca8cc920046",

	"0x222/2018/01/01/112233/2f28a1251954505c565199094a9cc6bf46315a19",
	"0x222/2018/01/02/112234/e7b98b96bd95ebdd2c97f99fde9379321ab92a73",
	"0x222/2018/01/03/112233/ad7d3f958b9561345aadc324e1468b5a7f30633d",

	"0x333/2018/01/01/112234/1ebb9cbeab77210850d57210c5b921c21fb99db5",
	"0x333/2018/01/02/112235/71a5c7ebcd8755a4e9f1e8ebb5dfda9ee699aba0",
	"0x333/2017/12/22/231833/7ee5135f34507329252d9918d46c512d279d479b",
	"0x333/2017/12/22/231833/edabce13530b0a48307acb0ad197ab05c37780ae",
};

rocksdb::DB* db;

static void query_account_tx(const std::string& account)
{
	printf("account query: %s\n", account.c_str());

	// open cursor
	rocksdb::Iterator* it = db->NewIterator(rocksdb::ReadOptions());

	// build an in-the-future, just-beyond-our-key prefix
	string pfx = account + "/2019";

	// seek to first key we want to match (last, lexicographically)
	it->Seek(pfx);
	if (it->Valid())
		it->Prev();
	else	it->SeekToLast();

	// reverse iterate ("from Z to A"), starting with seek'd key
	for (; it->Valid(); it->Prev()) {
		const string key = it->key().ToString();

		// match ETH account?  if not, stop.
		if (key.substr(0, account.size()) != account)
			break;

		// print matching transaction for given account
		printf("pq %s\n", key.c_str());
	}

	assert(it->status().ok());
	delete it;
}

static void doit()
{
	map<string,bool> memdb;

	// load raw data into key/value memory db
	for (auto it = rawdata.begin(); it != rawdata.end(); it++)
		memdb[*it] = true;

	// dump memory db, from last (most recent) to first
	for (auto it = memdb.rbegin(); it != memdb.rend(); it++)
		printf("memdb %s\n", it->first.c_str());

	// create/open on-disk rocksdb
        rocksdb::Options options;
        options.create_if_missing = true;
        rocksdb::Status status =
          rocksdb::DB::Open(options, "eth-tx-prefix-db", &db);
        assert(status.ok());

	// load raw data into rocksdb.  value is constant for all keys.
	const string val = "1";
	for (auto it = rawdata.begin(); it != rawdata.end(); it++) {
        	rocksdb::Status s =
			db->Put(rocksdb::WriteOptions(),
				*it, val);
        	assert(s.ok());
	}

	// dump rocksdb, from last (most recent) to first
	rocksdb::Iterator* it = db->NewIterator(rocksdb::ReadOptions());
	for (it->SeekToLast(); it->Valid(); it->Prev()) {
		printf("rocksdb %s\n",
			it->key().ToString().c_str());
	}
	assert(it->status().ok());
	delete it;

	// example "query eth account for most recent transactions" queries
	query_account_tx("0x333");
	query_account_tx("0x222");
	query_account_tx("0x111");
}

int main (int argc, char *argv[])
{
	doit();
	return 0;
}

Example output

memdb 0x333/2018/01/01/112234/1ebb9cbeab77210850d57210c5b921c21fb99db5
memdb 0x333/2017/12/22/231833/edabce13530b0a48307acb0ad197ab05c37780ae
memdb 0x333/2017/12/22/231833/7ee5135f34507329252d9918d46c512d279d479b
memdb 0x222/2018/01/03/112233/ad7d3f958b9561345aadc324e1468b5a7f30633d
memdb 0x222/2018/01/02/112234/e7b98b96bd95ebdd2c97f99fde9379321ab92a73
memdb 0x222/2018/01/01/112233/2f28a1251954505c565199094a9cc6bf46315a19
memdb 0x111/2018/01/02/112235/8f931e1414240217545feb29cfd51515ee653df2
memdb 0x111/2018/01/01/112234/6fa20f3b9249bee487b9b35fe6aed324a5b33e1d
memdb 0x111/2018/01/01/012200/21653edbb94b74071a3925267394f9ac419c292d
memdb 0x111/2017/12/22/231833/efa7fbc628d76f208f5ea2f5a5b04ca8cc920046
memdb 0x111/2017/12/22/231833/ecfea5128d3651a78df2088d60cec348ac5db104
rocksdb 0x333/2018/01/02/112235/71a5c7ebcd8755a4e9f1e8ebb5dfda9ee699aba0
rocksdb 0x333/2018/01/01/112234/1ebb9cbeab77210850d57210c5b921c21fb99db5
rocksdb 0x333/2017/12/22/231833/edabce13530b0a48307acb0ad197ab05c37780ae
rocksdb 0x333/2017/12/22/231833/7ee5135f34507329252d9918d46c512d279d479b
rocksdb 0x222/2018/01/03/112233/ad7d3f958b9561345aadc324e1468b5a7f30633d
rocksdb 0x222/2018/01/02/112234/e7b98b96bd95ebdd2c97f99fde9379321ab92a73
rocksdb 0x222/2018/01/01/112233/2f28a1251954505c565199094a9cc6bf46315a19
rocksdb 0x111/2018/01/02/112235/8f931e1414240217545feb29cfd51515ee653df2
rocksdb 0x111/2018/01/01/112234/6fa20f3b9249bee487b9b35fe6aed324a5b33e1d
rocksdb 0x111/2018/01/01/012200/21653edbb94b74071a3925267394f9ac419c292d
rocksdb 0x111/2017/12/22/231833/efa7fbc628d76f208f5ea2f5a5b04ca8cc920046
rocksdb 0x111/2017/12/22/231833/ecfea5128d3651a78df2088d60cec348ac5db104
account query: 0x333
pq 0x333/2018/01/02/112235/71a5c7ebcd8755a4e9f1e8ebb5dfda9ee699aba0
pq 0x333/2018/01/01/112234/1ebb9cbeab77210850d57210c5b921c21fb99db5
pq 0x333/2017/12/22/231833/edabce13530b0a48307acb0ad197ab05c37780ae
pq 0x333/2017/12/22/231833/7ee5135f34507329252d9918d46c512d279d479b
account query: 0x222
pq 0x222/2018/01/03/112233/ad7d3f958b9561345aadc324e1468b5a7f30633d
pq 0x222/2018/01/02/112234/e7b98b96bd95ebdd2c97f99fde9379321ab92a73
pq 0x222/2018/01/01/112233/2f28a1251954505c565199094a9cc6bf46315a19
account query: 0x111
pq 0x111/2018/01/02/112235/8f931e1414240217545feb29cfd51515ee653df2
pq 0x111/2018/01/01/112234/6fa20f3b9249bee487b9b35fe6aed324a5b33e1d
pq 0x111/2018/01/01/012200/21653edbb94b74071a3925267394f9ac419c292d
pq 0x111/2017/12/22/231833/efa7fbc628d76f208f5ea2f5a5b04ca8cc920046
pq 0x111/2017/12/22/231833/ecfea5128d3651a78df2088d60cec348ac5db104