<commit_before># Overview

The v2 codebase format is a sqlite3 database with some Unison objects stored relationally, and others stored as binary blobs.

There are a few important concepts for understanding the implementation.

**The `hash` and `text` tables**
`hash` stores Unison hashes in base32hex format. It is indexed to let us look up hashes efficiently by case-insensitive prefix in log time.  Hashes are not stored anywhere else in the database.

`text` stores all of the strings in the codebase. This includes definition names, internal names of ucm builtins, and user-defined strings. Though it doesn't include local variable definition names. Possibly an oversight. 😬  It is indexed to let us look up strings by exact match in logarithmic time, for deduplication purposes. Indeed, it does not contain any duplicates.

**The `object` table**

The object table stores things that are identified by hash and represented with binary blobs instead of relationally.  Currently that includes patches and namespace slices, along with term and decl components ("component" ambiguously meaning the whole cycle, a strongly-connected component of definitions).  Note that the entire component is identified by a single hash, like patches and namespace slices are.  There could be new object types in the future.  The object type of each object is given by the `type_id` field. (It's an integer, but the description is given in the `object_type_description` table for convenience, and in `ObjectType.hs`.)

**The `causal` and `causal_parents` tables**

```sql
CREATE TABLE causal (
  self_hash_id INTEGER PRIMARY KEY NOT NULL REFERENCES hash(id),
  value_hash_id INTEGER NOT NULL REFERENCES hash(id)
);

CREATE TABLE causal_parent (
  causal_id INTEGER NOT NULL REFERENCES causal(self_hash_id),
  parent_id INTEGER NOT NULL REFERENCES causal(self_hash_id)
);
```

Although causals are indexed by `hash.id`, an entry in the `causal` table is required, to qualify as a real causal. This also tells us the hash of its root namespace slice (which may or may not be present).

**Hash-based vs Object-based References**

To avoid referencing an object that is expected to be in the database but isn't actually in the database (e.g. a patch, a term dependency), most objects in the v2 codebase format are referenced by `object.id`.
<commit_msg>add more sql to v2.markdown<commit_after># Overview

The v2 codebase format is a sqlite3 database with some Unison objects stored relationally, and others stored as binary blobs.

There are a few important concepts for understanding the implementation.

**The `hash` and `text` tables**

```sql
CREATE TABLE hash (
  id INTEGER PRIMARY KEY NOT NULL,
  base32 TEXT UNIQUE NOT NULL COLLATE NOCASE
);
```

`hash` stores Unison hashes in base32hex format. It is indexed to let us look up hashes efficiently by case-insensitive prefix in log time.  Hashes are not stored anywhere else in the database.

```sql
CREATE TABLE text (
  id INTEGER PRIMARY KEY NOT NULL,
  text TEXT UNIQUE NOT NULL
);
```

`text` stores all of the strings in the codebase. This includes definition names, internal names of ucm builtins, and user-defined strings. Though it doesn't include local variable definition names. Possibly an oversight. 😬  It is indexed to let us look up strings by exact match in logarithmic time, for deduplication purposes. Indeed, it does not contain any duplicates.

**The `object` table**

```sql
CREATE TABLE object (
  id INTEGER PRIMARY KEY NOT NULL,
  primary_hash_id INTEGER NOT NULL REFERENCES hash(id),
  type_id INTEGER NOT NULL REFERENCES object_type_description(id),
  bytes BLOB NOT NULL
);
```
The object table stores things that are identified by hash and represented with binary blobs instead of relationally.  Currently that includes patches and namespace slices, along with term and decl components ("component" ambiguously meaning the whole cycle, a strongly-connected component of definitions).  Note that the entire component is identified by a single hash, like patches and namespace slices are.  There could be new object types in the future.  The object type of each object is given by the `type_id` field. (It's an integer, but the description is given in the `object_type_description` table for convenience, and in `ObjectType.hs`.)

**The `causal` and `causal_parents` tables**

```sql
CREATE TABLE causal (
  self_hash_id INTEGER PRIMARY KEY NOT NULL REFERENCES hash(id),
  value_hash_id INTEGER NOT NULL REFERENCES hash(id)
);

CREATE TABLE causal_parent (
  causal_id INTEGER NOT NULL REFERENCES causal(self_hash_id),
  parent_id INTEGER NOT NULL REFERENCES causal(self_hash_id)
);
```

Although causals are indexed by `hash.id`, an entry in the `causal` table is required, to qualify as a real causal. This also tells us the hash of its root namespace slice (which may or may not be present).

**Hash-based vs Object-based References**

To avoid referencing an object that is expected to be in the database but isn't actually in the database (e.g. a patch, a term dependency), most objects in the v2 codebase format are referenced by `object.id`.

{'fraction_non_alphanumeric': 0.05648944487881157, 'fraction_numerical': 0.0025410476935105552, 'mean_word_length': 4.685555555555555, 'pattern_counts': {'":': 0, '<': 3, '<?xml version=': 0, '>': 3, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '3923003', 'n_tokens_mistral': 1407, 'n_tokens_neox': 1305, 'n_words': 748}