);

CREATE TABLE name_lookups (

root_branch_hash_id INTEGER PRIMARY KEY REFERENCES hash(id) ON DELETE CASCADE

);

CREATE TABLE scoped_term_name_lookup (

root_branch_hash_id INTEGER NOT NULL REFERENCES hash(id) ON DELETE CASCADE,

-- The name of the term in reversed form, with a trailing '.':

-- E.g. map.List.base.

--

-- The trailing '.' is helpful when performing suffix queries where we may not know

-- whether the suffix is complete or not, e.g. we could suffix search using any of the

-- following globs and it would still find 'map.List.base.':

-- map.List.base.*

-- map.List.*

-- map.*

reversed_name TEXT NOT NULL,

-- The last name segment of the name. This is used when looking up names for

-- suffixification when building PPEs.

-- E.g. for the name 'base.List.map' this would be 'map'

last_name_segment TEXT NOT NULL,

-- The namespace containing this definition, not reversed, with a trailing '.'

-- The trailing '.' simplifies GLOB queries, so that 'base.*' matches both things in

-- 'base' and 'base.List', but not 'base1', which allows us to avoid an OR in our where

-- clauses which in turn helps the sqlite query planner use indexes more effectively.

--

-- example value: 'base.List.'

namespace TEXT NOT NULL,

referent_builtin TEXT NULL,

referent_component_hash TEXT NULL,

referent_component_index INTEGER NULL,

referent_constructor_index INTEGER NULL,

referent_constructor_type INTEGER NULL,

PRIMARY KEY (root_branch_hash_id, reversed_name, referent_builtin, referent_component_hash, referent_component_index, referent_constructor_index)

);

CREATE INDEX scoped_term_names_by_namespace_and_last_name_segment ON scoped_term_name_lookup(root_branch_hash_id, last_name_segment, namespace);

CREATE INDEX scoped_term_name_by_referent_lookup ON scoped_term_name_lookup(root_branch_hash_id, referent_builtin, referent_component_hash, referent_component_index, referent_constructor_index, namespace);

CREATE INDEX scoped_term_names_by_namespace ON scoped_term_name_lookup(root_branch_hash_id, namespace);

CREATE TABLE scoped_type_name_lookup (

root_branch_hash_id INTEGER NOT NULL REFERENCES hash(id),

-- The name of the term: E.g. List.base

reversed_name TEXT NOT NULL,

-- The last name segment of the name. This is used when looking up names for

-- suffixification when building PPEs.

-- E.g. for the name 'base.List.map' this would be 'map'

last_name_segment TEXT NOT NULL,

-- The namespace containing this definition, not reversed, with a trailing '.'

-- The trailing '.' simplifies GLOB queries, so that 'base.*' matches both things in

-- 'base' and 'base.List', but not 'base1', which allows us to avoid an OR in our where

-- clauses which in turn helps the sqlite query planner use indexes more effectively.

--

-- example value: 'base.List.'

namespace TEXT NOT NULL,

reference_builtin TEXT NULL,

reference_component_hash INTEGER NULL,

reference_component_index INTEGER NULL,

PRIMARY KEY (reversed_name, reference_builtin, reference_component_hash, reference_component_index)

);

CREATE INDEX scoped_type_names_by_namespace_and_last_name_segment ON scoped_type_name_lookup(root_branch_hash_id, last_name_segment, namespace)

CREATE INDEX scoped_type_name_by_reference_lookup ON scoped_type_name_lookup(root_branch_hash_id, reference_builtin, reference_component_hash, reference_component_index, namespace)

CREATE INDEX scoped_type_names_by_namespace ON scoped_type_name_lookup(root_branch_hash_id, namespace)