NeotomaDB / Neotoma_SQL

Repository for functions associated with Neotoma's Postgres DB
MIT License
1 stars 0 forks source link
neotoma paleoecology postgres sql

Neotoma_SQL

This is a repository to store the functions used within the Neotoma Database for data access and updates. The Neotoma is a Postgres-based system, migrated from a SQL Server database. As such, at the current time, this repository largely reflects the state of transition between the two systems.

Contributors

All individuals are welcome to contribute to this repository. Contributions are subject to the Code of Conduct for this repository.

Description

The repository is divided into two main folders, legacy and function:

This structure is not necessary, but has been implemented to help manage the workflow of rewriting the large number of functions associated with the original database.

Neotoma Postgres Databases

Neotoma maintains five separate databases within the PSU Postgres server. Of these five, three are significant, each has a specific function:

Associated Files

There are several files that have been used in the transition and are included here for posterity.

Maintaining the Repository

This repository is currently intended to be read-only with respect to the databases (neotoma, neotomadev and neotomatilia). The stored postgres functions can be updated using the Python3 script connect_remote.py by calling:

python3 connect_remote.py

This program checks the pg_catalog for the stated database and pulls each function within a defined set of namespaces, and returns each function as its own sql file to a folder in the function directory.

Using connect_remote.py

connect_remote.py has built in help that can be accessed using:

connect_remote.py -h
usage: connect_remote.py [-h] [-dev] [-push] [-g [PULLGIT]] [-tilia]

Check Neotoma SQL functions against functions in the online database servers
(`neotoma` and `neotomadev`).

optional arguments:
  -h, --help    show this help message and exit
  -dev          Use the `dev` database? (`False` without the flag)
  -push         Assume that SQL functions in the repository are newer, push to
                the db server.
  -g [PULLGIT]  Pull from the remote git server before running?.
  -tilia        Use the `dev` database? (`False` without the flag)

A user should make sure that local changes are committed and pushed before using the script, but in the case that changes were made directly to the GitHub repository online, they may use the -g flag.

Expected use should -push to each of the three databases.

Standards For New Functions

Functions are expected to follow (as much as possible) Simon Holywell's SQL Style Guide and the Postgres Coding Convention.

Existing Database Schema

For more insight into the structure of the Neotoma Database (specifically the ndb schema, which contains the core data of the database), visit the Neotoma Database Documentation. Documentation was generated using the SchemaSpy software.

Issues and Bugs

Please feel free to raise issues using the issue tracker on this repository.