week_3_day_1_morning.rb

##Loading data from a CSV File
#Slow
#Memory expensive
#Buggy

##DATABASE
#A different program; 
#Create a string that describes what you want and send it to the database; 
#You could have the database on a different machine/different country, all you need to send to it is a query string; 

#SQL invented in the 1970s; 
#SQLite3(light weight solution, not good for a webserver that has billions of requests per minute)
#PostgresQL(proffessional database, very optimised); 
#SQL and PostgresQL are interchangable; 
#SQL is a relational database; 
#No SQL (such as MONGODB and CouchDB) they are NOT relational, they are giant hashes of JSON data; 

#SQlite lives inside all iPhones and android devices; 
#SQL is case insensitive; 

CREATE TABLE person(
  id INTEGER PRIMARY KEY,
  first_name TEXT,
  last_name TEXT,
  age INTEGER
)

#Each row of the table represents a single person; 
#A record is a single row of the table; 
#We can read from the database, write to the database, update the database, and delete records from it; 
#Any database needs to be able to: create, read, update and delete => CRUD; 
#To create: INSERT
#To read: SELECT
#Update: UPDATE
#Delete: DELETE

#person_pet table is relational table, relating a particular person with a particual pet;

sqlite3 test.db < pets.sql #running the .sql file in the test.db; 

sqlite3 test.db
CREATE TABLE test (id);

.schema #tells you about the database; 

##INERTING DATA
INSERT INTO person(id, first_name, last_name, age) VALUES (
DROP TABLE test; #To drop a table

#SELECT
SELECT * from person; #select everything
SELECT first_name FROM person; 
SELECT name, breed FROM pet;

#DELETE
/* make sure there's dead pets */
SELECT name, age FROM pet WHERE dead = 1;

/* aww poor robot */
DELETE FROM pet WHERE dead = 1;

#UPDATE
#You want to change some columns of the record; 
#UPDATE person SET first_name = "Hilarious Guy"
  UPDATE person SET first_name = "Hilarious Guy"
    WHERE first_name = "Zed";

  UPDATE pet SET name = "Fancy Pants"
    WHERE id=0; #This is the safer way of doing it!

##RELATIONSHIPS
#ONE PERSON HAS MANY PETS; 
#ONE TO MANY, MANY TO MANY;
#person_pet table, a person can have many pets and a pet can have many persons; 
#The person_pet table does not have an id, we do not need it; 

#sqlite3 Gem provides the ability to for the webserver to talk to the database; 

##TERMINAL INTERFACES
#To get into sqlite: type sqlite3 into terminal; 
#To quit .quit or ctrl + d;

#To create multiple files
mkdir{public,public/css.views}

#which sqlite3 (shows you where it is on the machine)