ECE366 - Lesson 3
Postgres
Instructor: Professor Hong
## Docker for Abstract Classes
Dockerfile
```
FROM eclipse-temurin:latest
ADD . /app
WORKDIR /app
CMD javac abstraction/*.java; java -cp . abstraction/ShapeTester
```
## Mounting Dockers to Your Local Drive
hello.java
```
public class Hello {
public static void main(String[] args) {
System.out.println("Hello Chris!");
}
}
```
Dockerfile
```
FROM eclipse-temurin:latest
ADD . /app
WORKDIR /app
CMD ["java", "Hello.java"]
```
## Mounting Dockers to Your Local Drive
```
Docker Command
```
$ docker run -i -v $(pwd):/app --name hello-app hello
```
```-v``` mounts your docker to the directory you specify
```
## What is Postgres?
- AKA PostgreSQL
- Free and open-source (FOSS) relational database management system (RDBMS)
- Started at the University of California, Berkeley
- ACID compliant and transactional
- Atomicity, Consistency, Isolation, Durability
- Has materialized and updatable views
- Trigger support
- Functions and stored procedure support
- Runs on every major OS (and in Docker)
## CRUD
- The 4 main functions you do in a database
- Create
- Read
- Update
- Delete
## Running Postgres in Docker
```
$ docker pull postgres
$ mkdir -p ~/srv/postgres
$ docker run --rm --name lil-postgres -e POSTGRES_PASSWORD=password -d -v $HOME/srv/postgres:/var/lib/postgresql/data -p 5432:5432 postgres
```
- This pulls Postgres from docker hub
- Creates a directory to store the data on your computer
- Runs Postgres and sets the username and password, mounts it to your computer, and runs it on port 5432
- ```-rm``` cleans up the image after running
## psql
Using psql
```
psql -h localhost -U postgres
```
Create a Database
```
CREATE DATABASE mystore;
GRANT ALL PRIVILEGES ON DATABASE mystore TO postgres;
```
Connect to a Database
```
\c mystore
```
## Creating A Table
In psql, database mystore
```
CREATE SEQUENCE hp_customer_seq start with 10000;
CREATE TABLE customer (
customer_id bigint NOT NULL DEFAULT nextval('hp_customer_seq'),
first_name varchar(50) DEFAULT NULL,
last_name varchar(50) DEFAULT NULL,
email varchar(50) DEFAULT NULL,
phone varchar(50) DEFAULT NULL,
address varchar(50) DEFAULT NULL,
city varchar(50) DEFAULT NULL,
state varchar(50) DEFAULT NULL,
zipcode varchar(50) DEFAULT NULL,
PRIMARY KEY (customer_id)
);
```
## Viewing a Schema
In psql
```
\d customer
```
Postgres column types can be found [here](https://www.postgresql.org/docs/current/datatype.html).
## DBeaver
You can also install this app to connect to your database: [DBeaver](https://dbeaver.io/)
## Inserting Records to a Table (Create)
Insert Data to customer
```
INSERT INTO customer (customer_id, first_name, last_name, email, phone, address, city, state, zipcode)
VALUES
(100,'Carol','Shaw','cshaw0@mlb.com','(206)804-8771','8157 Longview Court','Seattle','WA','98121'),
(101,'Elizabeth','Carr','ecarr1@oracle.com','(512)187-2507','3934 Petterle Trail','Austin','TX','78732'),
(102,'Ernest','Ramos','eramos2@plala.or.jp','(816)540-4257','8699 Clarendon Terrace','Kansas City','MO','64199'),
(103,'Jane','Carter','jcarter3@harvard.edu','(214)839-0542','2830 Novick Lane','Irving','TX','75037'),
(104,'Martha','Cooper','mcooper4@go.com','(727)235-5696','4537 Hoard Lane','Tampa','FL','33625'),
(105,'Patricia','Fox','pfox5@hubpages.com','(240)937-3491','40748 Stoughton Parkway','Frederick','MD','21705')
(106,'Patricia','Fox','pfox5@hubpages.com','(240)937-3491','40748 Stoughton Parkway','Frederick','MD','21705');
```
## Selecting Records from a Table (Read)
```
SELECT * FROM customer;
SELECT first_name FROM customer;
SELECT count(*) FROM customer;
SELECT count(distinct(first_name)) FROM customer;
```
## Updating Records in a Table (Update)
```
UPDATE customer SET last_name='Foo' WHERE customer_id=106;
```
## Deleting Records from a Table (Delete)
```
DELETE FROM customer WHERE customer_id=106;
```
## Primary Key
- Must exist (non-null)
- Uniquely identifies the row of data
## Another Table
salesperson
```
CREATE SEQUENCE hp_salesperson_seq start with 10000;
CREATE TABLE salesperson (
salesperson_id bigint NOT NULL DEFAULT nextval('hp_salesperson_seq'),
first_name varchar(50) DEFAULT NULL,
last_name varchar(50) DEFAULT NULL,
email varchar(50) DEFAULT NULL,
phone varchar(50) DEFAULT NULL,
address varchar(50) DEFAULT NULL,
city varchar(50) DEFAULT NULL,
state varchar(50) DEFAULT NULL,
zipcode varchar(50) DEFAULT NULL,
PRIMARY KEY (salesperson_id)
);
```
## Insert into salesperson
```
INSERT INTO salesperson (salesperson_id, first_name, last_name, email, phone, address, city, state, zipcode)
VALUES
(100,'Jack','Powell','jpowell0@mystore.com','(434)951-5046','5 Jenifer Crossing','Lynchburg','Virginia','24515'),
(101,'Emily','Garcia','egarcia1@mystore.com','(603)489-3196','97 Vidon Alley','Manchester','New Hampshire','3105'),
(102,'Richard','Dean','rdean2@mystore.com','(713)474-6460','2482 1st Road','Houston','Texas','77228'),
(103,'Jane','Porter','jporter3@mystore.com','(703)355-7761','5230 Rigney Circle','Alexandria','Virginia','22301'),
(104,'Robin','Vasquez','rvasquez4@mystore.com','(915)388-4102','7 Upham Alley','El Paso','Texas','79984'),
(105,'Douglas','Flores','dflores5@mystore.com','(832)915-9358','144 Banding Lane','Houston','Texas','77090'),
(106,'Craig','Johnston','cjohnston6@mystore.com','(505)817-9381','35301 Burning Wood Park','Las Cruces','New Mexico','88006');
```
## Foreign Key
- Maintains referential integrity of the data
- If a column is a foriegn key, it refers to a column in another table
- This column's data must also exist in the other table
## A Table with Foreign Keys
```
CREATE SEQUENCE hp_order_seq start with 10000;
CREATE TABLE orders (
order_id bigint NOT NULL DEFAULT nextval('hp_order_seq'),
creation_date timestamp DEFAULT NULL,
total_due numeric(10,2) DEFAULT NULL,
status varchar(50) DEFAULT NULL,
customer_id bigint NOT NULL,
salesperson_id bigint NOT NULL,
PRIMARY KEY (order_id),
FOREIGN KEY (customer_id) REFERENCES customer(customer_id),
FOREIGN KEY (salesperson_id) REFERENCES salesperson(salesperson_id)
);
```
## Inserting data into orders
Invalid Inserts
```
INSERT INTO orders (order_id, creation_date, total_due, status, customer_id, salesperson_id)
VALUES
(1000,'2016-05-14 00:00:00',118.22,'paid',413,130),
(1001,'2016-05-14 00:00:00',118.22,'paid',400,101),
(1002,'2016-05-14 00:00:00',118.22,'paid',101,444);
```
Valid Inserts
```
INSERT INTO orders (order_id, creation_date, total_due, status, customer_id, salesperson_id)
VALUES
(1000,'2016-05-14 00:00:00',118.22,'paid',101,100),
(1001,'2016-05-14 00:00:00',118.22,'paid',102,100),
(1002,'2016-05-14 00:00:00',118.22,'paid',103,100);
```
## On Cascade Delete
Deleting a customer
```
DELETE FROM customer WHERE customer_id=101;
```
Adding the cascade
```
ALTER TABLE orders
DROP CONSTRAINT orders_customer_id_fkey,
ADD CONSTRAINT orders_customer_id_fkey
FOREIGN KEY (customer_id)
REFERENCES customer(customer_id)
ON DELETE CASCADE;
```
- ```ON DELETE CASCADE``` allows you to delete all references to the base table's row
## Designing a Tic-Tac-Toe MMO Database
## What Tables Do We Want?
- Class Discussion
## Creating our tables
- Create .sql files