====== Technical Reference ======

~~NOTOC~~

===== Command Line Access (SSH Tunnel from laptop) =====

((with thanks to Mohamad S. for the suggestion)) From a local terminal window open up an SSH tunnel through mote to our postgres server:
<code>
$ ssh -L 5555:postgres:5432 -p 443 username@mote.cs.vassar.edu
</code>
\\ where ''username'' is the name of your CS account. 

Then from a second local terminal window access our postgres server directly from the command line:
<code>
$ psql -h localhost -p 5555 -U username -d dbname
</code>
\\ where ''username'' is (again) the name of your CS account, and ''dbname'' is the name of the database (e.g., cap).

===== Command Line Access (from Linux workstation) =====

Our postgres server can be accessed from the command line via ''psql'' (the PostgreSQL interactive terminal):
<code>
$ psql -h postgres -U username -d dbname
</code>
\\ where ''username'' is name of your CS account and ''dbname'' is the name of your database. 
You will be prompted to enter your password. You should enter your Linux CS account password, not your Vassar password.
For more information about the ''psql'' command see: https://www.postgresql.org/docs/11/app-psql.html

For example: 
<code>
mlsmith@mote:~$ psql -h postgres -U mlsmith -d university
Password for user mlsmith: 
psql (10.10 (Ubuntu 10.10-0ubuntu0.18.04.1))
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

university=# 
</code> \\

From within ''psql''  there, a few useful commands are:
<code>
\dt          -- to display metadata for the tables in the database
\d tablename -- to display metadata for a particular table in the database
\?           -- to display help on psql command line options
\h           -- to display help on SQL commands 
\q           -- to quit out of psql and return to the linux prompt ($)
</code>

\\ For example:
<code>
university=# \dt
           List of relations
 Schema |    Name    | Type  |  Owner  
--------+------------+-------+---------
 public | advisor    | table | mlsmith
 public | classroom  | table | mlsmith
 public | course     | table | mlsmith
 public | department | table | mlsmith
 public | instructor | table | mlsmith
 public | prereq     | table | mlsmith
 public | section    | table | mlsmith
 public | student    | table | mlsmith
 public | takes      | table | mlsmith
 public | teaches    | table | mlsmith
 public | time_slot  | table | mlsmith
(11 rows)

university=# \d instructor
                     Table "public.instructor"
  Column   |         Type          | Collation | Nullable | Default 
-----------+-----------------------+-----------+----------+---------
 id        | character varying(5)  |           | not null | 
 name      | character varying(20) |           | not null | 
 dept_name | character varying(20) |           |          | 
 salary    | numeric(8,2)          |           |          | 
Indexes:
    "instructor_pkey" PRIMARY KEY, btree (id)
Check constraints:
    "instructor_salary_check" CHECK (salary > 29000::numeric)
Foreign-key constraints:
    "instructor_dept_name_fkey" FOREIGN KEY (dept_name) REFERENCES department(dept_name) ON DELETE SET NULL
Referenced by:
    TABLE "advisor" CONSTRAINT "advisor_i_id_fkey" FOREIGN KEY (i_id) REFERENCES instructor(id) ON DELETE SET NULL
    TABLE "teaches" CONSTRAINT "teaches_id_fkey" FOREIGN KEY (id) REFERENCES instructor(id) ON DELETE CASCADE

university=# 
</code>

\\ The other useful--and cool--thing you can do from inside ''psql'' is issue SQL commands. For example:
<code>
university=# select * from instructor;
  id   |    name    | dept_name  |  salary  
-------+------------+------------+----------
 10101 | Srinivasan | Comp. Sci. | 65000.00
 12121 | Wu         | Finance    | 90000.00
 15151 | Mozart     | Music      | 40000.00
 22222 | Einstein   | Physics    | 95000.00
 32343 | El Said    | History    | 60000.00
 33456 | Gold       | Physics    | 87000.00
 45565 | Katz       | Comp. Sci. | 75000.00
 58583 | Califieri  | History    | 62000.00
 76543 | Singh      | Finance    | 80000.00
 76766 | Crick      | Biology    | 72000.00
 83821 | Brandt     | Comp. Sci. | 92000.00
 98345 | Kim        | Elec. Eng. | 80000.00
(12 rows)

university=# 
</code>
\\ \\