How to read the Tangle database?

Question

For infinite number of reasons it might be interesting to access transaction history stored on the Tangle. I tried to do that myself, because I was interested in how many addresses are at risk because of reuse, but right now I'm stuck and I don't know how to move on. Here is what I did so far.

1. I don't run a full node, so I downloaded the Tangle database from http://iota.partners (step 6. Database)
2. I studied IRI source code a bit and discovered it's using RocksDB as the database engine, so I created a new java project and referenced RocksJava and IRI.

3. Struggled for a while how to actually make it work, but I was finally able to read some data from the database. Here is my source code so far (I was just trying to make it work, don't expect any pretty coding here):

   RocksDB.loadLibrary();
   
   final String databasePath = "d:\\tmp\\mainnetdb\\";
   final List<String> columnFamilyNames = Arrays.asList("transaction", "transaction-metadata", "milestone",
           "stateDiff", "address", "approvee", "bundle", "tag");
   
   final List<ColumnFamilyDescriptor> myColumnFamilyDescriptors = columnFamilyNames.stream()
           .map((x) -> new ColumnFamilyDescriptor(x.getBytes())).collect(Collectors.toList());
   
   final List<ColumnFamilyDescriptor> columnFamilyDescriptors = new ArrayList<>();
   columnFamilyDescriptors.add(new ColumnFamilyDescriptor(RocksDB.DEFAULT_COLUMN_FAMILY));
   columnFamilyDescriptors.addAll(myColumnFamilyDescriptors);
   
   // a list which will hold the handles for the column families once the db is
   // opened
   final List<ColumnFamilyHandle> columnFamilyHandles = new ArrayList<>();
   try (final DBOptions options = new DBOptions().setCreateIfMissing(false)
           .setCreateMissingColumnFamilies(false)) {
       try (RocksDB db = RocksDB.openReadOnly(options, databasePath, columnFamilyDescriptors,
               columnFamilyHandles)) {
           try {
               for (int f = 0; f < columnFamilyHandles.size(); f++) {
                   ColumnFamilyHandle cfh = columnFamilyHandles.get(f);
                   RocksIterator it = db.newIterator(cfh);
   
                   int i = 0;
                   for (it.seekToFirst(); it.isValid(); it.next()) {
                       i++;
                   }
   
                   if (f > 0) { // skip default
                       System.out.println(columnFamilyNames.get(f - 1) + ": " + i);
                   }
               }
           } finally {
               db.close();
           }
       } finally {
           options.close();
       }
   }
   

4. This is supposed to iterate over column families (similar to tables in relational db) and print the number of items in each of them. The output I'm getting is:

   transaction: 19
   transaction-metadata: 22
   milestone: 13661
   stateDiff: 0
   address: 0
   approvee: 0
   bundle: 0
   tag: 0
   

5. For the transactions that are actually present, I'm able to call https://github.com/iotaledger/iri/blob/dev/src/main/java/com/iota/iri/model/Transaction.java#L91 to deserialize the byte[] and convert it to usable data, but ...

Why are there so few transactions? The database is around 9-10 GB, so I would expect that to be the entire Tangle history, or am I wrong? Am I missing something here? Or am I accessing the database incorrectly?

---

PROGRESS: So after checking out the source for Python IOTA library here: https://github.com/mlouielu/iota-python/blob/dev/iotapy/storage/providers/rocksdb.py#L70 (that code is actually not used, but anyway ...) I tried to set merge operators through ColumnFamilyOptions:

    final ColumnFamilyOptions cfo = new ColumnFamilyOptions().setMergeOperatorName("stringappend");

    final List<ColumnFamilyDescriptor> myColumnFamilyDescriptors = columnFamilyNames.stream()
            .map((x) -> new ColumnFamilyDescriptor(x.getBytes(), cfo)).collect(Collectors.toList());

and now it's producing different output for these column families:

address: 19
approvee: 25
bundle: 31
tag: 20

Answer 1

Ok, so after trying to reinvent a wheel for a while I actually discovered this class - https://github.com/iotaledger/iri/blob/dev/src/main/java/com/iota/iri/storage/rocksDB/RocksDBPersistenceProvider.java which provides an abstraction from underlying RocksDB and contains all the settings necessary to access the data in the proper way.

RocksDBPersistenceProvider rocks = 
            new RocksDBPersistenceProvider(databasePath, databasePath + "mainnet.log" /* not sure about this one ??? */, 8192 /* cache size in KB */);

rocks.init();

Pair<Indexable, Persistable> txPair = rocks.first(Transaction.class, Hash.class);

Hash hash = (Hash) txPair.low; // transaction id
Transaction tx = (Transaction) txPair.hi; // transaction

// ...

I see no way how to iterate through the entire database with existing API, but I can easily create a copy or use reflection and uncover the underlying RocksDB instance and create an iterator.

---

Update: Ok, so after trying the mentioned solution with the iteration I got the same results as before (0 transations) ... but at least it was much less work with setting up the database. Nevertheless I tried to do a graph walk using the tx.branch and tx.trunk references and got a org.rocksdb.RocksDBException: Snappy not supported or corrupted Snappy compressed block contents when trying to retrieve a transaction by given hashes. I have no solution for that yet.

One more thing to mention is that when I do rocks.count(Transaction.class) it gives me a reasonable count of 5.5 million records.

---

Update: So the problem seems to be in the database being compressed using Snappy compression even though the latest IRI versions doesn't use it anymore. I tried to look through github history, particularly at this: https://github.com/iotaledger/iri/blame/dev/src/main/java/com/iota/iri/storage/rocksDB/RocksDBPersistenceProvider.java#L501, but it was there just for a couple of hours before it was commented out.

Also I'm doing this on Windows, don't know if that might be a problem.

Guy here reported similar problem: https://github.com/facebook/rocksdb/issues/1887 - same symptoms as mine.

Snappy, snappy ... you're killing me.