# Tika Eval H2 Query

> Query the tika-eval H2 database directly for counts and joins the canned reports do not compute — connection gotchas, key tables, example queries. Use when the xlsx/summary.md reports are not enough.

- Skill: `apache/tika-eval-h2-query` (Agent Skill)
- Install (CLI): `npx skillmds@latest add apache/tika-eval-h2-query`
- Raw SKILL.md: https://api.skillmd.com/api/skills/apache/tika-eval-h2-query/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Data & Analytics
- Author: apache (https://skillmd.com/u/apache)
- Updated: 2026-09-17
- Page: https://skillmd.com/skills/apache/tika-eval-h2-query

---


<!--
Licensed to the Apache Software Foundation (ASF) under one or more
contributor license agreements.  See the NOTICE file distributed with
this work for additional information regarding copyright ownership.
The ASF licenses this file to You under the Apache License, Version 2.0
(the "License"); you may not use this file except in compliance with
the License.  You may obtain a copy of the License at

    http://www.apache.org/licenses/LICENSE-2.0

Unless required by applicable law or agreed to in writing, software
distributed under the License is distributed on an "AS IS" BASIS,
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
See the License for the specific language governing permissions and
limitations under the License.
-->

Local override: `$TIKA_SKILLS_LOCAL/tika-eval-h2-query/LOCAL.md` (default `~/.tika-skills`),
read after this file, wins on conflict.

# Query the tika-eval H2 database directly

`tika-eval` (Compare / Profile / Report) stores everything in an **H2** database
— the `-d <name>` you pass to `Compare` produces `<name>.mv.db`. The xlsx/`summary.md`
reports only surface pre-canned views; for anything else (exact counts, custom
joins, "better vs worse" tallies the reports don't compute) connect to the H2 db
and run SQL.

## Connecting (the part that trips people up)

tika-eval creates the db with **no username and no password** (`H2Util` calls
`DriverManager.getConnection(url)` with no creds). So:

- **URL:** `jdbc:h2:<absolute-path-without-the-.mv.db-suffix>`
- **user / password: empty.** `-user sa` (H2's old default) **fails** with
  "Wrong user name or password" — the db wasn't created with `sa`.
- Add `;IFEXISTS=TRUE` so a typo opens nothing instead of silently creating a new
  empty db, and `;ACCESS_MODE_DATA=r` for a safe read-only open.
- **H2 is single-writer**: don't query while a `Compare`/`Profile`/`Report` run
  has the db open (file lock). Query after the run finishes.

Use the `h2-*.jar` that ships with tika-eval-app (in its `target/dependency/` or
the unzipped runtime `lib/`).

### One-shot query (H2 Shell)

```bash
H2=path/to/h2-*.jar
DB='jdbc:h2:path/to/eval;IFEXISTS=TRUE;ACCESS_MODE_DATA=r'   # note: no .mv.db
java -cp "$H2" org.h2.tools.Shell -url "$DB" -user '' -password '' \
  -sql "SHOW TABLES"
```

Pass the SQL as a **single line** to `-sql` (a multi-line arg confuses the Shell;
it then waits on stdin and appears to hang).

## Key tables

| table | what's in it |
|---|---|
| `PROFILES_A` / `PROFILES_B` | one row per extracted file: `FILE_NAME`, `MD5`, `MIME_ID`, `CONTAINER_ID`, `EMBEDDED_FILE_PATH`, `LENGTH`, `NUM_PAGES`, … (A = "before"/-a, B = "after"/-b) |
| `CONTENTS_A` / `CONTENTS_B` | text profile per file (join on `ID`): `OOV`, `LANGUAGENESS`, `NUM_TOKENS`, `NUM_COMMON_TOKENS`, `LANG_ID_1`/`LANG_ID_PROB_1`, `TOKEN_ENTROPY_RATE`, … |
| `ENCODINGS_A` / `ENCODINGS_B` | detected-encoding per file (join on `ID`): `DETECTED_ENCODING`, `ENCODING_DETECTOR`, `DECLARED_METADATA`. **`DETECTED_ENCODING` lives HERE, not on `PROFILES` — moved out in the encodings-table refactor; querying `PROFILES_*.DETECTED_ENCODING` now errors "Column not found".** A file with no detected encoding has no row. |
| `CONTENT_COMPARISONS` | per-file A↔B comparison (`ID`): `DICE_COEFFICIENT`, `OVERLAP`, top token diffs |
| `MIMES` | `MIME_ID` → `MIME_STRING` |
| `CONTAINERS` | container id → input file path |
| `*_COMPARED` (`EXCEPTIONS_COMPARED`, `PARSE_TIME_COMPARED`) | pre-joined A/B summaries |

## Example queries

Detected charset distribution in the "after" run (fast):

```sql
SELECT m.MIME_STRING, COUNT(*) n
FROM PROFILES_B p JOIN MIMES m ON p.MIME_ID = m.MIME_ID
GROUP BY m.MIME_STRING ORDER BY n DESC LIMIT 20;
```

Worst content divergences (already A/B-paired, fast):

```sql
SELECT ID, DICE_COEFFICIENT FROM CONTENT_COMPARISONS
WHERE DICE_COEFFICIENT < 0.5 ORDER BY DICE_COEFFICIENT;
```

OOV / languageness better-vs-worse tally. **A and B are paired by `id`** — the
same row `id` is the same file in both runs (this is exactly how tika-eval's own
reports join: `join profiles_b pb on pa.id = pb.id`). Join on `id`, never on
container/path — that's a fast PK join:

```sql
SELECT SUM(CASE WHEN cb.OOV < ca.OOV THEN 1 ELSE 0 END) AS oov_better,
       SUM(CASE WHEN cb.OOV > ca.OOV THEN 1 ELSE 0 END) AS oov_worse
FROM CONTENTS_A ca JOIN CONTENTS_B cb ON ca.ID = cb.ID;
```

Net common-tokens A vs B — the headline "more real text recovered" metric (a big
positive delta is the usual sign of better charset/extraction in B):

```sql
SELECT SUM(ca.NUM_COMMON_TOKENS) AS common_a,
       SUM(cb.NUM_COMMON_TOKENS) AS common_b,
       SUM(cb.NUM_COMMON_TOKENS) - SUM(ca.NUM_COMMON_TOKENS) AS delta
FROM CONTENTS_A ca JOIN CONTENTS_B cb ON ca.ID = cb.ID;
```

To bring in mime/path, join `PROFILES_A pa ON pa.ID = ca.ID` (and `pb`/`cc`
likewise on the same `id`) — all on `id`.

Detected-encoding queries — `DETECTED_ENCODING` is on `ENCODINGS_A`/`ENCODINGS_B`
(join on `ID`), NOT `PROFILES`. CJK count in B (LOWER() — `REGEXP` is case-sensitive,
see below):

```sql
SELECT COUNT(*) FROM ENCODINGS_B
WHERE LOWER(DETECTED_ENCODING) REGEXP 'gb|big5|euc|shift|jis|2022|949';
```

Encoding flips A→B by direction (what changed between runs):

```sql
SELECT ea.DETECTED_ENCODING a_enc, eb.DETECTED_ENCODING b_enc, COUNT(*) n
FROM ENCODINGS_A ea JOIN ENCODINGS_B eb ON ea.ID = eb.ID
WHERE ea.DETECTED_ENCODING <> eb.DETECTED_ENCODING
GROUP BY a_enc, b_enc ORDER BY n DESC;
```

Map a flipped file back to its source file — `PROFILES_*.FILE_NAME` is the content
hash (the input file is `<corpus>/<first-2-hex>/<FILE_NAME>`); join `CONTENTS` for OOV:

```sql
SELECT pb.FILE_NAME, ea.DETECTED_ENCODING a_enc, eb.DETECTED_ENCODING b_enc,
       ca.OOV oov_a, cb.OOV oov_b
FROM ENCODINGS_A ea JOIN ENCODINGS_B eb ON ea.ID = eb.ID
  JOIN PROFILES_B pb ON ea.ID = pb.ID
  JOIN CONTENTS_A ca ON ea.ID = ca.ID JOIN CONTENTS_B cb ON ea.ID = cb.ID
WHERE LOWER(eb.DETECTED_ENCODING) REGEXP 'gb|big5|euc|shift|jis|2022|949'
  AND NOT (LOWER(ea.DETECTED_ENCODING) REGEXP 'gb|big5|euc|shift|jis|2022|949');
```

## Gotcha: `REGEXP` is case-sensitive (silent wrong results)

H2's `REGEXP` operator is **case-sensitive**, so `DETECTED_ENCODING REGEXP
'big5|gb|euc'` does **not** match `Big5-HKSCS` or `GB18030` — and it fails
*silently*, quietly dropping/keeping the wrong rows instead of erroring. Always
either lowercase the column or use the inline case-insensitive flag:

```sql
-- right:
WHERE LOWER(DETECTED_ENCODING) REGEXP 'big5|gb|euc|shift|jis|2022|949'
-- or:
WHERE DETECTED_ENCODING REGEXP '(?i)big5|gb|euc|shift|jis|2022|949'
-- wrong (misses Big5-HKSCS, GB18030, Shift_JIS, ...):
WHERE DETECTED_ENCODING REGEXP 'big5|gb|euc|shift|jis|2022|949'
```

(`DETECTED_ENCODING` is on `ENCODINGS_A`/`ENCODINGS_B` — join to `PROFILES`/`CONTENTS`
on `ID` — populated from `tk:detected-encoding`.)

## Tip

For a quick interactive session, drop `-sql` and you get an H2 prompt; `SHOW
COLUMNS FROM <table>;` lists a table's columns.