Using and accessing the r4st database

Patrick N. McGovern 2023

Description 

The 2023 Open4st Cloud Drive provides view access to Openst database data for family, clones, field trials, nursery and research data.  It leverages Google Drive, Postgresql database and the Insync dropbox to synchronize local Open4st files to Google drive.   The previous (pre-2022) online r4st database application used the open source Postgresql database and the online DBKiss database application for view access to database tables and views.  Both systems were designed to be used as a central repository by importing/exporting data via csv files, creating SQL queries for specific reports and views for "big picture" summaries for R Programming and further analysis.  While the online database application was very robust, it required login access and regular security maintenance updates on local and remote servers.  The simpler Cloud Drive system can be maintained on the local system and provides user view access to Google Drive for the same CSV files.

An open source development copy of the Open4st database (aka “r4st”) is available via the pmcgover/24dev-demo GitHub public repository.  It is a prototyping add-on process for the OSGeo Live DVD which allows the user to review, modify, and execute the open source code using the MIT license.  To access this material, review the 24dev-demo documentation, download the latest 24dev-demo release and install it on a OSGeoLive system. The r4st database documentation provides a high level description of the 24dev-demo process.  The r4st/csv folder contains the CSV files that are loaded into the database with scripts from the r4st/bin folder to create the tables and views.  This is essentially a build process that drops and recreates all of the existing data each time the scripts are activated and allows for easy modifications that could be deactivated and used long term without the build process. 

Accessing the Open4st online database

• Navigate to:  r4st online database.   Request the credentials via our Contact Form.

Accessing the new Open4st Cloud Files

• Users need a Google Drive account to view files under:  Open4st-Database

• The main Open4st-Database folder contains database named folders along with the AAA_README.txt file for general database file access details.  Simply sort the files in this directory to view the alphabetical sorted priority.

• Each database folder contains CSV files prefixed with the following alphabetical naming conventions for easier sorting:

  • "AT_"  Primary database tables copied to CSV files.  Where the "A" denotes the alphabetical sort priority and the "T" denotes a table.

  • "BV_" Primary database views copied to CSV files.    Where the "B" denotes the alphabetical sort priority and the "V" denotes a view.   Note that other view prefixes are categorized with different alpha characters represented by their related view descriptions.

  •  Users can click on any CSV file to view the general contents and download or share to their Google Drive as needed.

  • Some CSV files have been converted to Google Drive "gdsheet" files for easier viewing. 

  • Users can download all files in zipped format by right clicking on the folder name then "Download".

  • Users can access each file/folder URL link by right clicking on the file/folder and select "Get Link".   File links retain the same URL despite file content changes.

• The r4p database folder contains the main database files for the Open4st project.

• The r4t database folder contains IPC Salix cultivar related files related to the published manuscript, “ShortCommunication: IPC Salix Cultivar Database Proof-of-Concept”.  It also references the supplementary technical document: Technical Information: IPC Salix Cultivar Database Proof-of-Concept.

Significant r4st Tables

Database tables represent structured, related data in a row and column format.

• Plant, family, and taxa tables:  All new families, even with germination are included in the "family" table to allow reporting of all family and related parents. New plants are typically entered into the "plant" table when they have met the program selection criteria.  Both tables should map to the "taxa" species description table.

• Test Tables:  Routine activities such as annual nursery or field trials are considered cumulative testing events to promote the concept of, "Everything is a test".  The "test_spec" table contains high level test specifications with event (e.g. nursery) or trial (e.g field trial) details that map to the "test_detail" or "field_trial" tables describing the test detail events.  These tables should also map to the "site" table.

• Journal Table:  Serves as a blog for general observations.  Can be linked to other tables.

• Pedigree Table: Contains pedigree details for each family table entry by associating them with plant table records starting at the plant root level and listing each possible combination, sometimes requiring multiple lines.  The "Path" column lists each filial generation with the parent names in parentheses.    

Significant r4st Views

Database views: Represent a custom, virtual data set of one or more tables.  See "Open4st Database Column and Naming Descriptions" for more database column details on Open4st family and plant naming conventions.

AT_pedigree.csv: Table listing of pedigree details used by the Open4st program.

BV_family.csv:  View listing of families used by the Open4st program.

BV_plant.csv:  View listing of plants (mostly aspen clones) used by the Open4st program.

BV_test_detail.csv:  View listing of all test details (mostly annual nursery data) produced by the Open4st program.

BV_field_trial.csv:  View listing of Open4st field trial data.

FV_01_field_trial_master_summary.csv:  View listing of Open4st level 1 master summary field trial data.

r4st Database Schema

• View this  r4st-DatabaseDiagram that describes the r4st database relationships.

• For copyright details see the: open4st Disclaimer and Copyright.