db.qmd

# Database

## Database naming conventions

> There are only two hard things in Computer Science: cache invalidation and naming things. -- Phil Karlton (Netscape architect)

We're circling the wagons to come up with the best conventions for naming. Here are some ideas:

- [Learn SQL: Naming Conventions](https://www.sqlshack.com/learn-sql-naming-conventions/)
- [Best Practices for Database Naming Conventions - Drygast.NET](https://drygast.net/blog/post/database_naming_conventions)

### Name tables

- Table names are singular and use all lower case.

### Name columns

- To name columns, use [**snake-case**](https://cran.r-project.org/web/packages/snakecase/vignettes/introducing-the-snakecase-package.html) (i.e., lower-case with underscores) so as to prevent the need to quote SQL statements. (TIP: Use [`janitor::clean_names()`](https://sfirke.github.io/janitor/reference/clean_names.html) to convert a table.)

- Unique **identifiers** are suffixed with:
  - `*_id` for unique integer keys;
  - `*_uuid` for universally unique identifiers as defined by [RFC 4122](https://datatracker.ietf.org/doc/html/rfc4122) and stored in Postgres as [UUID Type](https://www.postgresql.org/docs/current/datatype-uuid.html).
  - `*_key` for unique string keys;
  - `*_seq` for auto-incrementing sequence integer keys.

- Suffix with **units** where applicable (e.g., `*_m` for meters, `*_km` for kilometers, `degc` for degrees Celsius). See [units vignette](https://cran.r-project.org/web/packages/units/vignettes/measurement_units_in_R.html).

- Set geometry column to **`geom`** (used by [PostGIS](https://postgis.net) spatial extension). If the table has multiple geometry columns, use `geom` for the default geometry column and `geom_{type}` for additional geometry columns (e.g., `geom_point`, `geom_line`, `geom_polygon`).

## Use Unicode for text

The [default character encoding for Postgresql](https://www.postgresql.org/docs/current/multibyte.html#MULTIBYTE-SETTING) is unicode (`UTF8`), which allows for international characters, accents and special characters. Improper encoding can royally mess up basic text.

Logging into the server, we can see this with the following command:

```bash
docker exec -it postgis psql -l
```

```
                                  List of databases
        Name        | Owner | Encoding |  Collate   |   Ctype    | Access privileges 
--------------------+-------+----------+------------+------------+-------------------
 gis                | admin | UTF8     | en_US.utf8 | en_US.utf8 | =Tc/admin        +
                    |       |          |            |            | admin=CTc/admin  +
                    |       |          |            |            | ro_user=c/admin
 lter_core_metabase | admin | UTF8     | en_US.utf8 | en_US.utf8 | =Tc/admin        +
                    |       |          |            |            | admin=CTc/admin  +
                    |       |          |            |            | rw_user=c/admin
 postgres           | admin | UTF8     | en_US.utf8 | en_US.utf8 | 
 template0          | admin | UTF8     | en_US.utf8 | en_US.utf8 | =c/admin         +
                    |       |          |            |            | admin=CTc/admin
 template1          | admin | UTF8     | en_US.utf8 | en_US.utf8 | =c/admin         +
                    |       |          |            |            | admin=CTc/admin
 template_postgis   | admin | UTF8     | en_US.utf8 | en_US.utf8 | 
(6 rows)
```

Use Unicode (`utf-8` in Python or `UTF8` in Postgresql) encoding for all database text values to support international characters and documentation (i.e., tabs, etc for markdown conversion).

- In **Python**, use [**`pandas`**](https://pandas.pydata.org/docs/index.html) to read ([`read_csv()`](https://pandas.pydata.org/docs/reference/api/pandas.read_csv.html)) and write ([`to_csv()`](https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.to_csv.html)) with UTF-8 encoding (i.e., `encoding='utf-8'`).:
  
  ```python
  import pandas as pd
  from sqlalchemy import create_engine
  engine = create_engine('postgresql://user:password@localhost:5432/dbname')
  
  # read from a csv file
  df = pd.read_csv('file.csv', encoding='utf-8')
  
  # write to PostgreSQL
  df.to_sql('table_name', engine, if_exists='replace', index=False, method='multi', chunksize=1000, encoding='utf-8')
  
  # read from PostgreSQL
  df = pd.read_sql('SELECT * FROM table_name', engine, encoding='utf-8')
  
  # write to a csv file with UTF-8 encoding
  df.to_csv('file.csv', index=False, encoding='utf-8')
  ```

- In **R**, use [**`readr`**](https://readr.tidyverse.org/index.html) to read ([`read_csv()`](https://readr.tidyverse.org/reference/read_delim.html)) and write ([`write_excel_csv()`](https://readr.tidyverse.org/reference/write_delim.html)) to force UTF-8 encoding.
  
  ```r
  library(readr)
  library(DBI)
  library(RPostgres)
  
  # connect to PostgreSQL
  con <- dbConnect(RPostgres::Postgres(), dbname = "dbname", host = "localhost", port = 5432, user = "user", password = "password")
  
  # read from a csv file
  df <- read_csv('file.csv', locale = locale(encoding = 'UTF-8'))  # explicit
  df <- read_csv('file.csv')                                       # implicit
  
  # write to PostgreSQL
  dbWriteTable(con, 'table_name', df, overwrite = TRUE)
  
  # read from PostgreSQL
  df <- dbReadTable(con, 'table_name')
  
  # write to a csv file with UTF-8 encoding
  write_excel_csv(df, 'file.csv', locale = locale(encoding = 'UTF-8'))  # explicit
  write_excel_csv(df, 'file.csv')                                       # implicit
  ```

## Ingest datasets with documentation

Use Quarto documents with chunks of R code in the [workflows](https://github.com/CalCOFI/workflows/) Github repository to ingest datasets into the database. For example, see the [ingest_noaa-calcofi-db](https://calcofi.io/workflows/ingest_noaa-calcofi-db.html) workflow.

```{mermaid}
%%| label: fig-db_doc
%%| fig-cap: "Database documentation scheme."
%%| file: diagrams/db_doc.mmd
```

Google Drive \*.csv files get ingested with a **workflow** per **dataset** (in Github repository [calcofi/workflows](https://github.com/calcofi/workflows) as a Quarto document). Data definition CSV files (`tbls_redefine.csv` , `flds_redefine.csv`) are auto-generated (if missing) and manually updated to rename and describe tables and fields. After injecting the data for each of the tables, extra metadata is added to the `COMMENT`s of each table as JSON elements (links in markdown), including at the ***table*** level:

-   **description**: general description describing contents and how each row is unique
-   **source**: CSV (linked to Google Drive source as markdown)
-   **source_created**: datetime stamp of when source was created on GoogleDrive
-   **workflow**: html (rendered Quarto document on Github)
-   **workflow_ingested**: datetime of ingestion

And at the ***field*** level:

-   **description**: general description of the field
-   **units**: using the International System of Units (SI) as much as possible

These comments are then exposed by the API [db_tables](https://api.calcofi.io/db_tables) endpoint, which can be consumed and rendered into a tabular searchable catalog with [calcofi4r::cc_db_catalog](https://calcofi.io/calcofi4r/reference/cc_db_catalog.html).

Additional workflows will publish the data to the various [Portals](https://calcofi.io/docs/portals.html) (ERDDAP, EDI, OBIS, NCEI) using ecological metadata language (EML) and the [EML](https://docs.ropensci.org/EML/) R package, pulling directly from the structured metadata in the database (on table and field definitions).

### OR Describe tables and columns directly

- Use the `COMMENT` clause to add descriptions to tables and columns, either through the GUI [pgadmin.calcofi.io](https://pgadmin.calcofi.io/) (by right-clicking on the table or column and selecting `Properties`) or with SQL. For example:

  ```sql
  COMMENT ON TABLE public.aoi_fed_sanctuaries IS 'areas of interest (`aoi`) polygons for federal **National Marine Sanctuaries**; loaded by _workflow_ [load_sanctuaries](https://calcofi.io/workflows/load_sanctuaries.html)';
  ```

- Note the use of [**markdown**](https://www.markdownguide.org/cheat-sheet/) for including links and formatting (e.g., bold, code, italics), such that the above SQL will render like so:
   
   > areas of interest (`aoi`) polygons for federal **National Marine Sanctuaries**; loaded by _workflow_ [load_sanctuaries](https://calcofi.io/workflows/load_sanctuaries.html)

- It is especially helpful to link to any _**workflows**_ that are responsible for the ingesting or updating of the input data.

### Display tables and columns with metadata

- These descriptions can be viewed in the CalCOFI **API** [api.calcofi.io](https://api.calcofi.io) as CSV tables (see code in [calcofi/api: `plumber.R`](https://github.com/CalCOFI/api/blob/8ad9d9ad62fd526d4b8da23357759f1ad196cb88/plumber.R#L916-L990)):
  - [api.calcofi.io`/db_tables`](https://api.calcofi.io/db_tables)\
    fields:\
    - `schema`: (only "public" so far)
    - `table_type`: "table", "view", or "materialized view" (none yet)
    - `table`: name of table
    - `table_description`: description of table (possibly in markdown)
  - [api.calcofi.io`/db_columns`](https://api.calcofi.io/db_columns)\
    fields:\
    - `schema`: (only "public" so far)
    - `table_type`: "table", "view", or "materialized view" (none yet)
    - `table`: name of table
    - `column`: name of column
    - `column_type`: data type of column
    - `column_description`: description of column (possibly in markdown)

- Fetch and display these descriptions into an interactive table with [`calcofi4r::`**`cc_db_catalog()`**](https://calcofi.io/calcofi4r/reference/cc_db_catalog.html).

## Relationships between tables

- See [calcofi/workflows: **clean_db**](https://calcofi.io/workflows/clean_db.html)\

- `TODO:` add calcofi/apps: db to show latest tables, columns and relationsips

## Spatial Tips

- Use [`ST_Subdivide()`](https://postgis.net/docs/ST_Subdivide.html) when running spatial joins on large polygons.