index.Rmd

--- 
title: "Neotoma Paleoecology Manual v2.0"
description: "The manual for the Neotoma Paleoecology Database, including SQL and R code examples."
author: "Simon Goring"
date: "`r Sys.Date()`"
site: bookdown::bookdown_site
documentclass: book
bibliography: [assets/references.bib]
biblio-style: apalike
link-citations: yes
delete_merged_file: true
always_allow_html: true
graphics: yes
url: https://neotomadb.org
output:
  bookdown::gitbook:
    css: ./style.css
    config:
      toc:
        max-depth: 4
      edit: null
      download: null
  bookdown::pdf_book:
    latex_engine: xelatex
    citation_package: natbib
    keep_tex: yes
  bookdown::epub_book: default
---

# Acknowledgements

The documentation for the [Neotoma Paleoecology Database](https://neotomadb.org) would not be possible without the extrordinary work of Dr. Eric C. Grimm [@Jacobson2021] who spent countless hours developing the original database manual, and was the center of an incredible community built around the database. Neotoma rests on the work of a number of researchers who contributed to the original North American Pollen Database, and subsequent data contributors, including FAUNMAP contributors and the data contributions of Allan Ashworth. The Neotoma Database would not exist were it not for the ongoing contributions of authors, data analysts and funding agencies, in particular the National Sciences Foundation. This manual draws heavily from Eric Grimm's original Neotoma manual (v2), published as [@grimm2008neotoma].

The Postgres snapshot of the database is accessible from [the Neotoma Snapshots page](http://www.neotomadb.org/snapshots). For users who may be interested in loading the database using Docker, a GitHub repository is available to install the latest snapshot and build a container locally.

```{r, packageload, echo=FALSE, messages=FALSE, warnings='false', results='hide', include=FALSE}
Sys.setenv(OPENSSL_CONF="/dev/null")
if (!require("pacman")) install.packages("pacman")
suppressPackageStartupMessages(library(pacman))
packages <- read.delim('requirements.txt')
p_load(char = unlist(packages))

dbLogin <- fromJSON('connect_remote.json')
db <- DBI::dbConnect(RPostgres::Postgres(),
                 dbname = dbLogin$database, 
                 host = dbLogin$host, 
                 port = dbLogin$port, 
                 user= dbLogin$user, 
                 password= dbLogin$password)

showTable <- function(db, tablename) {
  query <- "SELECT
                atr.attname AS column,
                pg_catalog.format_type(atr.atttypid, atr.atttypmod) AS data_type,
                array_to_string(array_remove(array_agg(DISTINCT con.conname), NULL), ', ') AS constraint
            FROM
                pg_catalog.pg_attribute AS atr
            INNER JOIN
                pg_catalog.pg_class AS cl ON cl.oid = atr.attrelid
            INNER JOIN
                pg_catalog.pg_namespace AS nsp ON nsp.oid = cl.relnamespace
            LEFT JOIN pg_catalog.pg_constraint AS con ON con.conrelid = atr.attrelid AND atr.attnum = ANY(con.conkey)
            WHERE
                atr.attnum > 0
                AND NOT atr.attisdropped
                AND nsp.nspname = 'ndb'
                AND cl.relname = $1
            GROUP BY atr.attname, atr.atttypid, atr.atttypmod, atr.attnum
            ORDER BY atr.attnum;"
  output <- dbGetQuery(db, query, tablename)
  DT::datatable(output, filter = 'none', rownames = FALSE, options = list(dom='ltip'))
}
```