How to back up the Cube Companion solves database

CubeCompanionBackup.md

How to back up your Cube Companion solve times on Ubuntu

The Cube Companion Android app lacks an export feature. Happily there's a quick way to extract the solves database so you can make fancy graphs or store your times somewhere else. This mini tutorial works on Ubuntu but it should work without many modifications on other Linux distros.

Activate USB debugging on Android device

• Go to Settings -> Developer options -> Turn ON Developer options -> Activate USB debugging

• Connect phone to PC through USB

Install requirements

• On the Ubuntu PC install Android Debug Bridge and SQLite CLI tool.

    sudo aptitude install android-tools-adb sqlite3
  

Extract the Cube Companion database

• Back up the Cube Companion data. This connects to the Android device, retrieves all the application data and saves it to a packed and compressed file cubecompanion.ab.

    adb backup -f cubecompanion.ab com.qbix.cubecompanion
  

• On the Android device accept the backup without entering a password. After it's done you can turn off Developer Options.

• Uncompress and unpack the backup. This creates a new directory apps which contains all the application data.

    dd if=cubecompanion.ab bs=24 skip=1 | zlib-flate -uncompress | tar xv
  

Open the solves database with SQLite CLI tool

• Enter the database directory

    cd apps/com.qbix.cubecompanion/db
  

• Open database

    sqlite3 solves.db
  

• Now you can query the database at will. Some examples:

  • Get your best time (ms) with 3x3:

      sqlite> select min(time) from puzzle3;
      18152
    

  • Get the number of 3x3 solves:

      sqlite> select count(*) from puzzle3;
      1686
    

  • Export all your 3x3 solves into a csv file:

      sqlite> .mode csv
      sqlite> .out solves.csv
      sqlite> select * from puzzle3;
      sqlite> CTRL-D
    

That's it :)