Week 04, Day 04

What we covered today:

• Warmup
  • Solution
• Demos
• SQL (Specific Query Language)
• CRUD
• Butterfly Lab

SQL

This is a really difficult topic and not one that we expect you to be able to write out - as long as you can get it in terms of principles - it is all good.

When we talk about tables and databases, there is really only 4 tasks that we need to do.

• Create
• Read
• Update
• Delete

This is called CRUD.

Involved in every database, there are a couple of things. We have:

• The Database itself
• Individual tables
• Individual records on tables

Before we can do any actual action we need to create the database to work with...

CREATE TABLE table_name (
    -- Comma seperated list of attributes with a type and a list of options
    column_name DATATYPE
);

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

This normally goes in a file with a file name ending with .sql. You might call this person.sql, and obviously we can have lots of these. As many as you want.

This hasn't created the database though, we need to be explicit about that.

sqlite3 desired_database_name.db < add_this_table.sql

sqlite3 database.db < person.sql

This line will create the database.db file if necessary, and if not - it will just add whatever is defined in the .sql file specified. It imports the details from the .sql into the database.db.

To make sure this has worked, type in sqlite3 database.db and hit enter in the terminal. This will open up a direct line to the database in the current folder. If you type .schema it will show the current tables.

CREATE STEP

Once we have the table defined, we need to figure out how to actually put records into it.

INSERT INTO table_name ( comma, seperated, columns ) VALUES ( commas_value, seperated_value, column_value);

INSERT INTO person (id, first_name, last_name, age) VALUES ( 0, "Zed", "Shaw", 37 );

-- We don't need to tell the attributes though, it can look just like this...

INSERT INTO person VALUES (0, "Zed", "Shaw", 37);

This is the creation step. If you wrote this in a file, we can import that SQL into the database - sqlite3 database_name.db < insert_stuff.sql

READ STEP

This is pretty annoying to write.

-- SELECT what FROM what_table;
-- SELECT what FROM what_table WHERE options;

SELECT * FROM person; -- this will select all attributes and all records from the person database

SELECT name FROM person; -- only show the name attributes

SELECT * FROM person WHERE first_name == "Zed"; -- show all attributes from records in the person database where the first_name is "Zed"

UPDATE STEP

And this is pretty annoying to write.

UPDATE table SET attribute_name = attribute_value WHERE attribute_name = attribute_value;

UPDATE person SET first_name = "WOLF" WHERE first_name = "Zed";

DELETE STEP

AND this is pretty annoying to write.

DELETE FROM what_table WHERE what_attributes = what_value;

DELETE FROM person WHERE first_name = "Zed"; -- Delete all records in the person table where the first_name is equal to "Zed"

That is the basics of SQL, for more - see here. It's all about the principles though, as long as you understand the fact that you need a database to have tables, and tables to have records - that is all good.

In terms of actual structure of an application, this is the structure of a CRUD application. 7 views for all of this! The #new and the #edit are just ways to show the actual form.

CRUD is the foundation of most applications on the web, it is the thing that powers it! Important to get the principles of it.

Here is the lab we did.