CS312 - Spring 2012 - Class 9

  • administrative
       - take-home quiz next week (will be available Monday)
       - homework 2 grades sent out
       - last assignment out soon
       - A word of warning: after the break, you're going to need to put regular time into this course

  • friends table
          sqlite> .schema
          CREATE TABLE friends (
           friend_id INTEGER PRIMARY KEY AUTOINCREMENT,
           last_name TEXT NOT NULL,
           first_name TEXT NOT NULL,
           email TEXT NOT NULL DEFAULT '',
           prof_id INTEGER /* foreign key */
          );
          CREATE TABLE professions (
           prof_id INTEGER PRIMARY KEY AUTOINCREMENT,
           name TEXT NOT NULL default ''
          );


  • select command: allows us to query the database

  • inserting into a table
       - so far, all we've done is query existing data in the table
       - we can both insert, update and delete data into the tables
       - INSERT: two basic forms:
          - specify all of the values
          INSERT INTO table_name VALUES (value1, value2, value3,...)

          - specify particular column values (assuming a default is allowed for that column)
          INSERT INTO table_name (column1, column2, column3,...) VALUES (value1, value2, value3,...)

          sqlite> insert into friends (last_name, first_name, email, prof_id) values("Z", "Bobby", "bobbyz@phoenix.edu", 5);
          sqlite> select * from friends;
          friend_id|last_name|first_name|email|prof_id
          1|Smith|Joe|jsmith@woohoo.com|1
          2|Lee|Phoebe|superchef@aol.com|4
          3|Speaker|Tony|ts@xyz.edu|5
          4|Johnson|Jennifer|jenniferj@medny.net|2
          5|Z|Bobby|bobbyz@phoenix.edu|5

       - NULL
          - NULL represents no value in sql (like a null pointer)
          - our friends table has 5 values
          - the first is an friends_id
             - we could specify this ourselves, but better to let sqlite just take care of it for us
             - we can say we don't have a value by specifying null

             sqlite> insert into friends values(NULL, "Z", "Bob", "bob@aol.com", 2);
             sqlite> select * from friends;
             friend_id|last_name|first_name|email|prof_id
             1|Smith|Joe|jsmith@woohoo.com|1
             2|Lee|Phoebe|superchef@aol.com|4
             3|Speaker|Tony|ts@xyz.edu|5
             4|Johnson|Jennifer|jenniferj@medny.net|2
             5|Z|Bobby|bobbyz@phoenix.edu|5
             6|Z|Bob|bob@aol.com|2

             - if we didn't include the NULL we'd get an error, since we need to specify 5 things

  • update
       - sometimes we don't want to insert a new entry, but just want to update some of the values of an entry
       - the update command allows us to do this
          UPDATE table_name
          SET column1=value, column2=value2,...
          WHERE some_column=some_value
          
          sqlite> select * from friends where last_name="Z";
          friend_id|last_name|first_name|email|prof_id
          5|Z|Bobby|bobbyz@phoenix.edu|5
          6|Z|Bob|bob@aol.com|2

          sqlite> update friends set email="bobby@aol.com", prof_id=1 where last_name='Z' AND first_name='Bob';
          sqlite> select * from friends where last_name="Z";
          friend_id|last_name|first_name|email|prof_id
          5|Z|Bobby|bobbyz@phoenix.edu|5
          6|Z|Bob|bobby@aol.com|1

       - be VERY careful with update!
          - you can update multiple entries if you're not careful
          - if you don't specify a WHERE clause, you'll update everything!
          - often a good idea to do a select to check your WHERE and then run the update

  • deleting from a table
       - DELETE allows you to remove entries from the table
       - it has similar syntax to select

          DELETE FROM table_name WHERE some_column=some_value

       - again, be VERY careful with delete!
          - you will delete any entry that matches the where
          - if you don't include a where, you will delete everything!
          - often a good idea to do a select to check your WHERE and then run the update

  • creating tables
       - as we've seen a database can contain multiple tables
       - before you can perform an insert (or a select, etc) you first need to create the table
       - in SQLite when we type .schema it actually shows us the statement that was used to create the table
          
          sqlite> .schema professions

          CREATE TABLE professions (
           prof_id INTEGER PRIMARY KEY AUTOINCREMENT,
           name TEXT NOT NULL default ''
          );

       - syntax for creating tables:
          
          CREATE TABLE <table_name>(
             <column_name1> <data_type> [constraints],
             <column_name2> <data_type> [constraints],
             <column_name3> <data_type> [constraints],
             <column_name4> <data_type> [constraints]
          )


       - the table name should describe the contents of the tables and often is made plural (by convention)
       - column names
          - should also be descriptive
          - if they contain multiple words we separate by underscores (by convention)
       - we've seen the different data types that you can have in SQLite
          - integer
          - text
          - numeric
          - real
          - none
       - there are a number of constraints (http://www.w3schools.com/sql/sql_constraints.asp), the ones we'll use most frequently:
          - NOT NULL
          - UNIQUE
          - PRIMARY KEY

       - specify all attributes that are applicable
          - it will help keep your data in good shape
          - it can help the performance of your queries
       - Even if you don't anticipate needing it, a table should almost always have a primary key (i.e. a unique id for each entry)
       - Many other things to think about when designing a database
          - there's an art to designing understandable and efficient database schemas
          - there are ways of improving performance by adding indexes, but that's beyond our scope

  • other structure changes to a database/table
       - you can remove a table from the database using the DROP commands
          - be careful this removes the table along with all the contents of the table

          DROP TABLE <table_name>

       - You can add or remove columns from a table using the ALTER command
          - you should try avoid this (particularly adding since it can be expensive and sometimes requires populating new data)

          ALTER TABLE <table_name> ADD <column_name> <data_type> [constraings]

          ALTER TABLE <table_name> DROP <column_name>

          - there are also other ways you can alter the table (look online)

  • running commands from a file
       - so far, we've run all of our commands/statements from the shell
       - you can also put your statements in a file and then run them all at once:

          sqlite3 my_database.db < my_commands.sql

       - one common place where this happens is when you get a "dump" of the database
          - a database dump are all the contents of the database in statements such that if they were issues, the database would be recreated
          - in sqlite3 the .dump command gives you this

          sqlite> .dump
          BEGIN TRANSACTION;
          DELETE FROM sqlite_sequence;
          INSERT INTO "sqlite_sequence" VALUES('professions',5);
          INSERT INTO "sqlite_sequence" VALUES('friends',4);
          CREATE TABLE professions (
           prof_id INTEGER PRIMARY KEY AUTOINCREMENT,
           name TEXT NOT NULL default ''
          );
          INSERT INTO "professions" VALUES(1,'Software Developer');
          INSERT INTO "professions" VALUES(2,'Medical Doctor');
          INSERT INTO "professions" VALUES(3,'Financial Analyst');
          INSERT INTO "professions" VALUES(4,'Chef');
          INSERT INTO "professions" VALUES(5,'Professor');
          CREATE TABLE friends (
           friend_id INTEGER PRIMARY KEY AUTOINCREMENT,
           last_name TEXT NOT NULL,
           first_name TEXT NOT NULL,
           email TEXT NOT NULL DEFAULT '',
           prof_id INTEGER /* foreign key */
          );
          INSERT INTO "friends" VALUES(1,'Smith','Joe','jsmith@woohoo.com',1);
          INSERT INTO "friends" VALUES(2,'Lee','Phoebe','superchef@aol.com',4);
          INSERT INTO "friends" VALUES(3,'Speaker','Tony','ts@xyz.edu',5);
          INSERT INTO "friends" VALUES(4,'Johnson','Jennifer','jenniferj@medny.net',2);
          COMMIT;

  • web servers
       - when you go to a url how do you get the html?
          - to contact another computer, you need to know its IP address (think of it like a phone number)
          - the url gets translated into an IP address
             - there are DNS servers (domain name servers) with known IP address that do this translation for you
          - once you have the IP address you issue an HTTP GET request from that computer
          - servers have ports
             - think of a port like a phone extension. the ip address is the main line into the computer and then the port tells you what program within the computer you should be talking through
             - there are some standard ports and some open
             http://en.wikipedia.org/wiki/List_of_TCP_and_UDP_port_numbers
          - a web server can run on any port 80 by default (though sometimes on 8080)
          - in response to your request, the server then issues the page
             - note that sometimes this is just a "static" pages like we've looked at in class so far
             - often, though, a "page" is dynamic and involves some computation/generation on the backend
                - this concept of dynamic page content has revolutionized web pages

  • ruby on rails
       "Ruby on a Rails an open-source web framework that's optimized for programmer happiness and sustainable productivity. It lets you write beautiful code by favoring convention over configuration"

       - created in 2003 (version 1.0 released in 2005)
       - built on top of Ruby
          - Ruby syntax and Ruby commands play an important role in Ruby on Rails
       - allows for quick development of web applications, i.e. dynamically generated web content
       - general framework tends to promote
          - separation between data, implementation and visualization
          - quick development cycles
          - incremental development (try something out, see the results and try again)

  • MVC setup
       - one of the key concepts behind Ruby is the Model View Controller framework
       - see Figure 4 on pg. 30 of the book
       - Model
          - behind most web applications is some data, often stored in a database
          - the model is responsible for
             - interfacing between the other components and the database
                - abstracts away the details of the underlying database, query language
             - providing the only way of accessing, editing and updating the data
             - allows for integrity checking, etc. all in one place
       - View
          - in charge of how the data is displayed and visualized
          - should not be doing much computation or calculation
          - in our case:
             - html
             - css
             - some Ruby interspersed
       - Controller
          - glues everything together
          - receive events and information from the outside world
          - handles all of the logic and queries

  • our first rails application
       - to setup a new project type:
          $ rails new demo

          - in response to this a lot of files will be created
          - if you're curious about the options for creating new projects
       
             $ rails new -h

       - if we look now, we have a directory called "demo" created (i.e. the name of the project)
       - inside the project directory are a bunch of directories and files
          - files:
             Gemfile
             README
             Gemfile.lock
             Rakefile
             config.ru

          - directories:
             app
             doc
             log
             script
             tmp
             config
             db
             lib
             public
             test
             vendor
          
          - pg. 257 in the book has a description of what is in each
          - don't be overwhelmed by all of these directories
             - the names are fairly intuitive
             - we'll introduce them slowly
       - a project defines a new application at the base of a web server
          - eventually, we would deploy this application to public facing web server
          - for now, though, we can run our own local web server
       - ruby comes with its own web server (or you can specify your own)
          - inside your project directory run

          $ rail server

          - this starts the web server with this project at its base
          - logging information, etc. will be printed out
          - to kill it, type Ctrl+C
       - we can now see what our current application is by going to:
          http://localhost:3000/

          - 'localhost' means this current computer
          - ':3000' says port 3000
          - note that this is not a publicly available web server, but will suffice for testing
       - the page that is displayed is the default starter page for a ruby application
       - the "public" directory contains the static web pages and is by default at the base of the application
          - if we look in the public directory we see a number of html files
       
             404.html 422.html 500.html favicon.ico index.html robots.txt
          - rails has generated all of this default information for us
          - if we look in index.html we see the html/css for the default web page we saw
       - let's change this:
          - rename the index.html page:
             $ mv index.html index.old.html
          - and now let's put in our own index.html file
             <! DOCTYPE html>
             <html>
             <head>
             <title>My own starter page</title>
             </head>
             <body>
             <h1>This is where it all starts...</h1>
             <body>
             </html>
          
          - now if we hit refresh on our web browser we see the updated content
       - inside the public directory, you can put any static content/pages you'd like and build up the static structures

  • dynamic content (controllers and views)
       - the power of ruby on rails, though, is dynamically generated content
       - to do this, though, we need to generate a new controller and view
          $ rails generate controller Name forward backwards
       
          - this tells rails to generate a controller (and associated files) called "Name" and it should have actions forward and backwards
       - each controller is by default routed as a subdirectory and each action it's own subdirectory from there
          http://localhost:3000/name/forward/
          http://localhost:3000/name/backwards/

       - the views
          - the view shows how the content is presented
          - in app/views/name/ we have views for these different actions/pages
          - all of the files are of type .html.erb
             - the .erb stands for "embedded ruby" and tells rails to process ruby commands nested in the html file
          - if we look in forward.html.erb we see
             <h1>Name#forward</h1>
             <p>Find me in app/views/name/forward.html.erb</p>

          - we can change this html and see the change in the page
             <h1>This is the name forward</h1>
             <p>Dave</p>
             <p>It has 4 characters in it</p>
          
             - notice that when we refresh the page, we see updated commands
  • erb: nesting ruby
       - we can nest ruby commands inside a .erb file in two ways
          - we can wrap code in <% ... %>
             - in this case the statement is run but nothing it output to the html file
          - we can wrap code in <%= ... %>
             - in this case, the return value of the statement is inserted into the html file
       - for example, if we change forward.html.erb to:

          <h1>This is the name forward</h1>
          <% name = "Dave" %>
          <p><%= name %></p>
          <p>It has <%= name.length %> characters in it (calculated automatically)</p>

          - the first embedded statement just creates the variable name
          - which we use in the next two embedded statements
       - notice that when we refresh, we see the updated content
          - if we look in the html all of these ruby commands have been replaced by text

  • the controller
       - let's update the backward page to show the name backwards
       - we could edit backwards.html.erb to be something like:
          h1>This is the name backward</h1>
          <% name = "Dave" %>
          <p><%= name.reverse %></p>

          - but if we ever wanted to change the name, we'd have to change it in both files
       - ruby also created a controller for us in app/controllers
       - if we look at name_controller.rb
          - a class that inherits from ApplicationController
          - we have three methods that are associated with our two actions
             - each method is called before the corresponding view is generated
       - adding a centralized name instance variable
          - to start with, let's add a name instance variable here that is shared between the views
          - lets add an initialize method that defines an instance variable @name
             def initialize
              super
               @name = "Dave"
             end

             - don't forget the call to super() (i.e. the parent class's initialize) otherwise things won't work correctly
          - now, we can go back to our view files and access this variable
             - the views associated with a controller have access to all of the instance variables as if they were inside the class
             <h1>This is the name forward</h1>
             <p><%= @name %></p>
             <p>It has <%= @name.length %> characters in it (calculated automatically)</p>
          - we can also change backwards to work appropriately with this variable
             <h1>This is the name backwards</h1>
             <p><%= @name.reverse %>
       - avoid work in the views
          - in general, the views should just be for displaying content
             - even though calling reverse is not a major computation, it hides implementation details in the view
          - we should move this computation to the controller
             - setup the controller to do all the reversing, etc

             def initialize
              super
              @name = "Dave"
              @reversed = false
              end

              def forward
              if @reversed
              @name = @name.reverse
              end
              end

              def backwards
              if not @reversed
              @name = @name.reverse
              end
              end


             - and then edit backwards to just display the name
             <h1>This is the name backwards</h1>
             <p><%= @name %>

  • linking between views
       - let's say we'd like to add links between forwards and backwards
       - we could put in a <a href=..., but it's very likely that this address wouldn't remain constant (we might move things, deploy somewhere else, etc.)
       - rails has a link_to function that allows us to link_to a page within the application
       - it also precomputes some paths for us to use in our program, in particular for ours:
          - name_forward_path contains the path to forward
          - name_backwards_path contains the path to backwards
       - using this, we could add the following to the bottom of forward to add a navigation bar:
          <p><%= link_to "Forward", name_forward_path %> <%= link_to "Backward", name_backwards_path %>


       


    •