Tika Eval H2 Query

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)

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

Example queries

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

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):

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:

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):

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):

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):

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:

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:

-- 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.