TourBook archive format

Your logbook belongs to you. Every backup TourBook writes is an ordinary ZIP file that holds an ordinary SQLite database, your photos and files as they are, and your GPS tracks in a small binary format described below. Standard tools read all of it: any unzip program, the sqlite3 command-line shell or a database browser, and a few lines of code for the tracks. TourBook does not need to be installed to get your data back out.

This page describes format version 2, written by every TourBook from 0.1.0 on.

The file

A backup is named tourbook-backup-YYYYMMDD-HHMMSS.tbx.zip, an export of selected tours tourbook-export-YYYYMMDD-HHMMSS.tbx.zip. The time is UTC. Both are plain ZIP archives (no ZIP64, so at most 4 GiB). Photos and other already-compressed files are stored as they are, everything else is compressed with Deflate.

<year> is the year of the tour's date. Every path below media/ and gpx/ is also stored in the database (media.rel_path, media.thumb_rel_path, gpx_track.geometry_rel_path, gpx_track.archive_rel_path), so each file belongs to exactly one row.

manifest.json

A JSON object with these fields:

counts covers these tables: tour, tour_segment, tour_person, tour_tag, tour_location, segment_grade, segment_condition, ascent, ascent_pitch, media, gpx_track, gpx_waypoint, person, tag, collection, region, location, location_attribute, location_grade_scale, route, route_pitch, route_grade, route_pitch_grade, segment_attribute, segment_attribute_value, segment_attribute_sport, segment_attribute_choice, attribute, challenge, challenge_target, challenge_target_alias, app_setting, tour_review, location_review, route_review, ascent_review, tour_pair_review, location_pair_review, route_pair_review, person_review, tag_review, region_group_review, collection_review, person_pair_review, tag_pair_review, region_group_pair_review, region_group, region_group_region, region_group_location, region_group_tour, tour_gpx_metrics, segment_gpx_metrics, location_region, segment_region, location_region_check, segment_region_check, segment_way_length, segment_way_check. Tours in the trash are counted too. An archive written by an earlier version may count fewer of them.

To check an archive by hand: unzip it and compare sha256sum of each file with its entry.

The database

db/tourbook.db is a regular SQLite 3 database, not encrypted. Its own schema is the reference: sqlite3 tourbook.db .schema lists every table, column and rule with comments. The tables that hold your logbook:

Change stamps. Each record that stands on its own (a tour, a location, a route, a partner, …) has a rev column: the value sync_clock had at the last change anywhere in that record, its parts included. It orders the changes of one logbook and says nothing about when they were made. sync_clock moves with every change of your data and with nothing else — not with what the app derives or records for itself — so it is also the logbook's change counter: the app remembers the value a backup archived, and "changed since the last backup" means the clock has moved since.

The remaining tables hold your settings (app_setting) and the app's own bookkeeping — what it records for this device, backup history, installed map packs, the schema version and the sync_clock — or are the vocabularies below. Archives of schema 72 also carry migration_journal, the log of the one-time import from TourBook 1, and location.source, enum_location_source and media.source_size_bytes/source_sha256, which only that import wrote; a restore of such an archive drops them.

Stored values are stable English keys, never translated text: a sport is hiking, ski_touring, climbing; an ascent style onsight, redpoint; a location kind peak, sector. Every vocabulary is a table of its own whose rows are the allowed keys: sport, enum_location_kind, enum_ascent_style, enum_ascent_role, enum_grade_family, enum_grade_scale, enum_route_character, enum_tour_location_role, enum_quality_check, condition_value. A grade scale names its family, a quality check what its review marks name (subject: tour, ascent, location, route, tourPair, locationPair, routePair).

A grade is a level of its family, never a token of a scale: one row per family (route_grade.family = rock, level = 57), whichever scale it was entered in. The families are alpine (SAC L … EX), rock (French, UIAA, YDS, Norwegian), boulder (Font, V), ice (WI), mixed (M), hiking (T), via_ferrata (K), snowshoe (WT) and mtb (S). For rock, boulder and ice the level is a step of OpenBeta sandbag's score axis (MIT); every other family's level is its scale's index (hiking: T1 = 0, T2 = 1, …). The token ranges of each scale on its family's axis are the app's src/data/domain/gradeAxis.generated.ts: a level reads as the token whose range holds it, else the nearest. Each token is stored at one level of its range — its middle, rounded down, except where the common Swiss/European table pairs a UIAA grade with a French one (IV+ = 4b, V- = 4c, V = 5a, V+ = 5b, VI+ = 6a). That table is the grade axis gradeAxisVersion names; the database records it as db_meta grade_axis.

The database carries that table too, as grade_token: one row per token of each scale (scale, token), its position in the scale, its range level_low–level_high on the family's axis and the level it is stored as. A scale names its family in enum_grade_scale, so a stored grade reads as its tokens without the app — per scale, the token whose range holds the level, else the nearest (a scale's ranges can leave gaps, UIAA between VII+ and VIII-):

SELECT family, scale, token FROM (
  SELECT g.family, t.scale, t.token,
         ROW_NUMBER() OVER (
           PARTITION BY g.family, t.scale
           ORDER BY MAX(t.level_low - g.level, g.level - t.level_high, 0), t.position
         ) AS nearest
  FROM route_grade g
  JOIN enum_grade_scale s ON s.family = g.family
  JOIN grade_token t ON t.scale = s.key
  WHERE g.route_id = ?
) WHERE nearest = 1;

Units are in the column name: _m metres (gain_m, elevation_m, length_m), _s whole seconds (duration_s), _km kilometres, _kmh km/h, _mh metres per hour, size_bytes bytes. Positions are WGS 84 degrees (lat −90 … 90, lon −180 … 180), both set or both NULL. Times are ISO 8601 text. A missing value is NULL, never 0; a distance, height gain or loss, duration, length or size is never negative, and no name is blank. The database's own CHECK constraints hold these rules. Identifiers are UUIDs (version 7) as text.

Tracks: the TBG1 format

A .tbg file holds one track as columns of fixed-size numbers, all little-endian:

Times are all or nothing: a track has a time for every point or for none. The columns follow each other without gaps; the file ends after the last one. A recording with several track segments is one track.

This JavaScript turns a .tbg into points and into a GPX file. It needs no library and runs in Node or a browser:

function readTbg(bytes) {
  const view = new DataView(bytes.buffer, bytes.byteOffset, bytes.byteLength);
  const magic = String.fromCharCode(...bytes.subarray(0, 4));
  if (magic !== 'TBG1') throw new Error('not a TBG1 track');
  const flags = view.getUint16(4, true);
  const count = view.getUint32(8, true);
  const t0 = view.getUint32(12, true);
  const hasHeight = (flags & 1) !== 0;
  const hasTime = (flags & 2) !== 0;
  const latAt = 16;
  const lonAt = latAt + 4 * count;
  const timeAt = lonAt + 4 * count;
  const heightAt = timeAt + (hasTime ? 4 * count : 0);
  const points = [];
  for (let i = 0; i < count; i++) {
    const height = hasHeight ? view.getInt16(heightAt + 2 * i, true) : -32768;
    points.push({
      lat: view.getInt32(latAt + 4 * i, true) / 1e7,
      lon: view.getInt32(lonAt + 4 * i, true) / 1e7,
      ele: height === -32768 ? null : height,
      time: hasTime ? new Date((t0 + view.getUint32(timeAt + 4 * i, true)) * 1000) : null,
    });
  }
  return points;
}

function tbgToGpx(bytes, name = 'Track') {
  const escape = (text) => text.replace(/&/g, '&amp;').replace(/</g, '&lt;').replace(/>/g, '&gt;');
  const points = readTbg(bytes).map((p) => {
    const ele = p.ele === null ? '' : `<ele>${p.ele}</ele>`;
    const time = p.time === null ? '' : `<time>${p.time.toISOString()}</time>`;
    return `<trkpt lat="${p.lat}" lon="${p.lon}">${ele}${time}</trkpt>`;
  });
  return [
    '<?xml version="1.0" encoding="UTF-8"?>',
    '<gpx version="1.1" creator="tbgToGpx" xmlns="http://www.topografix.com/GPX/1/1">',
    `<trk><name>${escape(name)}</name><trkseg>`,
    ...points,
    '</trkseg></trk>',
    '</gpx>',
  ].join('\n');
}

In Node, fs.writeFileSync('track.gpx', tbgToGpx(fs.readFileSync('track.tbg'))) writes the GPX next to it. The track's name, sport and tour are in the database: gpx_track.geometry_rel_path names the file, gpx_track.tour_segment_id its segment.

Versions

formatVersion changes only when the container itself changes. The database changes more often: every app update that changes a table raises schemaVersion. The schema's baseline is schemaVersion 72. TourBook restores an archive from any version since then and brings its database up to date while doing so; it refuses an archive written by a newer app than itself, and one written by a test version before the baseline (schemaVersion below 72). An export of selected tours is imported the same way: one from an older app is brought up to date first, one from a newer app is refused. Both need the same gradeAxisVersion. The grade axis is frozen: a version of the app that moves a grade's level raises gradeAxisVersion and converts the stored levels on upgrade; it refuses an archive on a newer axis than its own. Your copy of an older archive always stays readable with the tools above, since every version is a plain SQLite database.