Summary

Fixes #44.

On large builds the refs table dominates the analyze database — it repeated the property_path and property_type strings on every one of its (often millions of) rows. This normalizes those strings into two lookup tables so refs stores only integer ids, and exposes a refs_view that rejoins them so existing string-based queries barely change.

On a large customer project (5.8M refs rows) this reduces the database from 515 MB to 351 MB (-32%) with negligible impact on analyze time.

⚠️ Breaking schema change — how to adapt existing queries

This changes the refs table schema, so any tooling or SQL that reads refs.property_path / refs.property_type directly needs a small update. Those two columns are no longer text — they now hold integer ids into the new property_names / property_types tables.

Recommended fix: query refs_view instead of refs. The view has the same column names and the same text values the old refs table had (object, referenced_object, property_path, property_type), so in most cases it's a one-word change:

-- before
SELECT object, referenced_object, property_path, property_type
FROM refs
WHERE property_type = 'Texture2D';

-- after
SELECT object, referenced_object, property_path, property_type
FROM refs_view                    -- was: refs
WHERE property_type = 'Texture2D';

If you need to join refs to other tables yourself (e.g. for performance), resolve the strings through the lookup tables:

SELECT r.object, r.referenced_object, pn.name AS property_path, pt.name AS property_type
FROM refs r
JOIN property_names pn ON r.property_path = pn.id
JOIN property_types pt ON r.property_type = pt.id;

Queries that only use object / referenced_object (e.g. dependency traversal) are unaffected.

Databases now carry a schema version: PRAGMA user_version returns 1 for this format and 0 for databases produced by older builds. Tools can check it to fail fast (as find-refs now does) rather than hitting confusing errors. There is no in-place migration — regenerate the database by re-running analyze.

Changes

Schema (Analyzer/Resources/)

• New property_names and property_types lookup tables (id, name). refs.property_path / property_type change from TEXT to INTEGER ids into those tables.
• New refs_view reproduces the pre-change shape (object, referenced_object, property_path, property_type as text) so consumers read the strings without joining by hand.
• Added PRAGMA user_version = 1 — the first formal schema version (older databases report 0).
• view_material_shader_refs, view_material_texture_refs and script_object_view now read via refs_view.

Populating during analyze

• SerializedFileSQLiteWriter assigns string ids with two IdProviders and writes each lookup row once (new AddPropertyName / AddPropertyType commands, following the AddType pattern).

find-refs

• Reads references through refs_view; resolves the m_Script path to its id once per run rather than per object.
• Rejects databases older than schema version 1 with a clear "unsupported schema version — re-run analyze" message and non-zero exit, instead of an obscure SQL error.

Bonus fix

• view_material_texture_refs used double-quoted string literals ("Texture", "Material") that SQLite parses as identifiers, so the view errored. This was pre-existing (it failed on old databases too); fixed to single-quoted literals while rewriting the view.

Testing

• dotnet test — full suite green (UnityFileSystem 425, Analyzer 62, UnityDataTool 234).
• Fixed the one impacted test (BuildReportTests → refs_view).
• Added FindRefsTests coverage: refs_view round-trip, lookup-table consistency, and the unsupported-schema-version rejection.
• Manually verified on the large customer project: 32% smaller DB, find-refs renders property paths correctly (e.g. m_SavedProperties.m_TexEnvs[2].second.m_Texture), and running find-refs against a pre-change database exits 1 with the friendly message.