calcofi / docs Goto Github PK

Hi @mfrants, @edweber, @evsatt,

Here are some tips on how to document the PostgreSQL database...

Use COMMENT ON SQL for tables and columns

• How to Document PostgreSQL Databases, eg:

  -- comment on table
  COMMENT ON TABLE actor IS ‘Table containing actors’;
  
  --comment on column
  COMMENT ON COLUMN actor.actor_id IS ‘id of the actor’;

Entity-Relationship (ER) Diagram

• DBeaver Documentation. Note option to Show Comments and Export as graphML.
  
• SchemaCrawler - Free database schema discovery and comprehension tool

Use in R

By pulling into R, we can render these in the Quarto book Documenting CalCOFI at calcofi.io/docs and the R package calcofi4r .

Show Comments

• Metadata : from PostgreSQL comments to R labels – r.iresmi.net, eg with custom functions and Hmisc::label():

  # view table cog with Comments under column headers
  View(cog)

  

Show ER Diagram

• networkD3 to visualize interactive networks, including graphML

vs Rmarkdown b/c:

• easier to maintain
• newer/better

https://api.calcofi.io/ is too confusing for normal user. Provide more guidance on how to use the API functions with documented input arguments, output results and examples of use at https://calcofi.io/docs/api.html.