accessing VegBIEN¶

if you have a nimoy/vegbankdev SSH account:¶

this applies if you were a member of BIEN2. otherwise, see "if you have a vegbiendev database account" below.

• if you already have the BIEN2 bien_read password, use that
• otherwise:
  - for username, and when prompted for your password, use your nimoy/vegbankdev login
  • to get the password to access the main DB:
    $ ssh username@vegbiendev.nceas.ucsb.edu "cat /home/bien/config/bien_read_password; echo"
  • to get the password to access staging tables: (WARNING: this user has write access!)
    $ ssh username@vegbiendev.nceas.ucsb.edu "cat /home/bien/config/bien_password; echo"

to view the database schema:¶

1. go to *phpPgAdmin*
2. log in as user bien_read with the password you retrieved above
   (or to access staging tables, use user bien)
3. see steps under once logged in to phpPgAdmin below

to access data:¶

1. open Terminal/PuTTY
2. run a query:
   - you will also be prompted for the bien_read (or bien) password
   

   $ ssh bien_read@vegbiendev.nceas.ucsb.edu "$(cat <<'eof_ssh'
   psql --quiet -U bien_read -d vegbien --file=<(cat <<'eof_psql'
   COPY (
   SELECT * FROM "public".table LIMIT 100
   ) TO STDOUT CSV HEADER;
   eof_psql
   )
   eof_ssh
   )" 
   

   • to browse the DB:
     $ ssh username@vegbiendev.nceas.ucsb.edu "psql --quiet -U bien_read -d vegbien"
   • to export view_full_occurrence_individual:
     $ ssh username@vegbiendev.nceas.ucsb.edu "psql --quiet -U bien_read -d vegbien <<<'COPY (SELECT * FROM view_full_occurrence_individual LIMIT 100) TO STDOUT CSV HEADER;'"
   • to export analytical_stem:
     $ ssh username@vegbiendev.nceas.ucsb.edu "psql --quiet -U bien_read -d vegbien <<<'COPY (SELECT * FROM analytical_stem LIMIT 100) TO STDOUT CSV HEADER;'"
   • to export main-DB tables:
     $ ssh username@vegbiendev.nceas.ucsb.edu "psql --quiet -U bien_read -d vegbien <<<'COPY (SELECT * FROM table LIMIT 100) TO STDOUT CSV HEADER;'"
   • to export staging tables:
     $ ssh username@vegbiendev.nceas.ucsb.edu "psql --quiet -U bien -d vegbien <<<'COPY (SELECT * FROM \"datasource\".table LIMIT 100) TO STDOUT CSV HEADER;'"

if you have a vegbiendev database account:¶

this applies if you received an account setup e-mail with a temporary password. otherwise, see "if you have a nimoy/vegbankdev SSH account" above.

to view the database schema:¶

1. go to *phpPgAdmin*
2. log in as your user
3. see steps under once logged in to phpPgAdmin below

to access data:¶

1. open Terminal
2. $ sudo apt-get --yes install postgresql-9.3
3. run a query:
   

   $ psql --quiet -h vegbiendev.nceas.ucsb.edu -U username -d vegbien <<'eof'
   COPY (
   SELECT * FROM "schema".table LIMIT 100
   ) TO STDOUT CSV HEADER;
   eof
   

   • to browse the DB:
     $ psql --quiet -h vegbiendev.nceas.ucsb.edu -U username -d vegbien
   • to export view_full_occurrence_individual:
     $ psql --quiet -h vegbiendev.nceas.ucsb.edu -U username -d vegbien <<<'COPY (SELECT * FROM view_full_occurrence_individual LIMIT 100) TO STDOUT CSV HEADER;'
   • to export analytical_stem:
     $ psql --quiet -h vegbiendev.nceas.ucsb.edu -U username -d vegbien <<<'COPY (SELECT * FROM analytical_stem LIMIT 100) TO STDOUT CSV HEADER;'
   • to export tables:
     $ psql --quiet -h vegbiendev.nceas.ucsb.edu -U username -d vegbien <<<'COPY (SELECT * FROM table LIMIT 100) TO STDOUT CSV HEADER;'

once logged in to phpPgAdmin:¶

WARNING: do not use phpPgAdmin to access data, as this will crash the server. instead, use the steps under "to access data" above. phpPgAdmin can still be used to view the database schema.

to browse or configure a table:¶

1. In the left sidebar, expand vegbien > Schemas > public > Tables
2. Click a table
3. In the main frame, scroll all the way to the bottom and click Browse, Select, etc.

to run a query:¶

1. In the left sidebar, click vegbien
2. Click SQL in the upper tab bar
3. Enter a query, check Paginate results, and click Execute
4. To edit your query, scroll to the bottom of the page and click Edit SQL
5. To download the results, scroll to the bottom of the page and click Download