corpus-sql 0.4.0
Formulate and test queries prior to use in Jinja templates.
Requires Python: >=3.10,<4.0

# corpus-sql

Formulate and test server-side queries to an existing database; prequisites:

1. Requires table structures made in `corpus-db`, a separate library.
2. The path of the database is referenced in an `.env` file, either:
   1. `LOCAL_PATH` for the local filesystem's database `.db` file; or
   2. `DB_FILE` for the `.db` file within a volume of a Docker container.
3. A `filters.yaml` file must be populated.
4. Can set an optional `BASE_URL_PREFIX` and `UNIT_URL_PREFIX` in the `.env` file for fixing the absolute url for model objets; will default to `/detail/` and `/unit/`, respectively.

## Objects

### Usage

Objects are retrieved with dot notation:

```python
>>> from corpus_sql import Document
>>> doc = Document._from(pk='mv-2022-basic-law-v1', with_units=False)
>>> doc.__annotations__
{'pk': str,
 'kind': str,
 'url': str,
...
}
```

### Object Architecture

1. SQL files are built dynamically via Jinja.
2. The queried SQL files produce rows.
3. Each row is eventually converted from `sqlite3.Row` to a custom `dataclass`.
4. Each model is a `dataclass` contained in `corpus_sql/models/`
5. Each model's SQL files are contained in `corpus_sql/templates/<model>`

## Search

### Search Usage

Entity-based (filterable) searches are done by supplying a `page_num` parameter which will output the first 10 rows per page. If the `page_num` is omitted, the search will only return the first 3 resulting rows of the first page and exclude the key-value pairs after the `results` key:

```python
>>> from corpus_sql import Search
>>> Search(
    q="internet",
    input_entity="decisions",
    input_source="body_text",
    page_num=2).context # this gets the second page
{'q': '"internet"',
 'entity': 'decisions',
 'source': 'body_text',
 'page_num': 2,
 'sort_order': 'description',
 'max_count': 157,
 'results': [{'kind': 'decisions', # results key; value is a list
   'url': '/detail/decisions/66891',
   'title': 'Ip E-Game Ventures, Inc., Petitioner, Vs. Beijing Perfect World
   ... }]
}
```

### Search Architecture

In a nutshell, an entity's *full-text-search SQL statement* is made through: `/templates/<entity_name>/fts_<source_name>.sql`

#### /templates/entity_name

The `/entity_name` folder is the same model template folder.

#### /templates/entity_name/fts

The `/fts` subfolder implies that this `entity` will be used in [search.py](./corpus_sql/search.py)

#### /templates/entity_name/fts_source_name.sql

1. `/source_name/` in the path above refers to the **criteria to use**, or how the search will produce results.

2. To illustrate, in searching for the phrase *bureau of internal revenue* in the entity *Decisions*, I can match this phrase in the decision's **body text** column or in the decision's **title** column, i.e.

   1. `body_text`; or
   2. `case_title`.

3. Each of these are `source`s and for them to be valid filters usable by **search.py**, there must be *two paths*:

   1. `/templates/decisions/fts_body_text.sql`
   2. `/templates/decisions/fts_case_title.sql`

4. Each path contains three layers:
   1. Layer 1: Get the all relevant rowids matching the relevant `entity` with the `source` criteria
   2. Layer 2: Limit, sort range of rowids based on a given pagination supplied by `start_row` and `end_row` parameters
   3. Layer 3: With the limited range of rowids, sort and describe the `title`, `row_date`, `description` and even extraneous columns that will be processed in-app.

### Search Filters

For the new `entity` and its `sources` to be included in the search filters, supply the details in the `corpus_sql/filters.yaml`, e.g:

```yaml
slug: base # should be the default
is_selected: True # should be the default
units:
- item: Decisions # the label to display in the template
  slug: decisions # must be folder name; part of query string, if this is selected
  kind: entity # the kind of filter where this will be displayed
  units:
  - item: Text of Decisions
    slug: body_text
    kind: source
  - item: Title of Decisions # the label to display in the template
    slug: case_title # must be folder name; part of query string, if this is selected
    kind: source # the kind of filter where this will be displayed
- item: NewEntity # add a new entity here
```