CSC 321.01, Class 14: Modeling data, continued

Overview

• Preliminaries
  • Notes and news
  • Upcoming work
  • Extra credit
  • Questions
• A problem domain
• Updating the database with SQL
• Updating the database with Rails console

Preliminaries

News and notes

• Thanks to our mentor for covering class Monday and Today!
• Since I’ve had to miss a few classes, I may hold CSC 321 sessions during week eight. We’ll discuss that idea next week.

Upcoming work

• SaaSbook 7.1, 7.2, and Chapter 8 due Thursday at 9:00 pm. Please send your answers to both me and to Mr. Stone.

Good things to do (Academic/Artistic)

• Faulconer gallery has two great exhibits.
• Teacher Ed Symposium Thursday.
• Singers Concert Saturday.

Good things to do (Other)

Questions

Background

Common problem: We want to model data in our program.

As you’ve seen, a model is a collection of names/types that describe some kind of data.

Traditionally, we think about grouping these fields together in an object or in a table in which each row is one “thing” and the columns correspond to the fields.

We then want to do things with the tables

• I want all of the faculty members whose specialization is Vision
• I want all of the movies whose rating is PG
• I want all of the movies released before 1970
• I want all of the movies released before 1970 whose rating is PGa
• I want all of the users whose name includes the sequence “am”.

We often want to ask compound things, too

• I want the release dates of all the movies that Sam Rebelsky reviewed.

How do you implement this? Lots of strategies, most bad.

Solution: Build programs whose only job is to manage tabular data. The programs that manage this are Database Management Systems.

Note: Common syntax is probably important. SQL. Standard/structured query language, designed for working with data in the “related tables” format. (“Relational databases; “relation” is another name for “table”)

• SELECT columns FROM table
• SELECT columns FROM table WHERE restrictions
• SELECT columns FROM table1,table2 WHERE restrictions
  • SELECT reviews.reviewername FROM movies,reviews WHERE movie.moviename = 'Star Wars' AND review.movieid = movie.movieid
  • SELECT moviename FROM movies WHERE moviename LIKE "%ar"
• UPDATE table SET column=value WHERE ...
• INSERT columns VALUES values INTO table
• CREATE TABLE name ... (Sam always looks this one up)

These kinds of things are popular among the many popular implementations of SQL (MySQL, Oracle, SQLite, PostgeSQL,

There are also other models of data that are less standardized and less focused on tables.

Unfortunately, SQL misses many things you want to do.

• List all the tables in the database.
• Determine what columns are in a table.
• Write a script

Lab

1. Open your C9 account and navigate to your Hartl app. You app should currently have a User model, but likely no other models.

2. We’re going to mangle your database, so make a backup copy of db/development.sqlite3.

3. In a terminal tab, start the command-line database manager with rails dbconsole. This command gives you access to the native SQLite interface. We’ll refer to this as the “database consolse”.

4. In a separate terminal, type rails console. We’ll refer to this as the “rails console”.

5. In the rails console, create three or four new users. Review chapter 6 of Hartl if you’ve forgotten how to do so.

6. In the database console, type .help to determine what commands are available. (.help is a command specific to SQLite.)

7. In the database conosole, determine what tables are available by typing .tables. (The command is different in every DBMS.)

8. In the database console, determine the structure of the Users table by typing .schema Users.

9. In the database console, get all the user data with SELECT * FROM Users;

10. In the database console, get all the user names.

11. In the rails console, get all the user names. (Make sure that the two lists are the same.)

12. In the database console, get all the user ids and user names. Note that in the query, you will need to separate the fields with commas.

13. In the database console, get all the user ids plus names where the id is less than 3.

14. In the database console, get all the user ids plus names where the id is greater than 3.

15. In the database console, add two more users, specifying name and email.

    • INSERT into TABLE (fields) VALUES (values);

16. In the rails console, determine what happens with those users.

17. In the database console, try to add a user with a duplicate id.

18. Let’s suppose that our application allows users to provide feedback on each other. In the database console, create a new table, feedback, that contains the following columns. (You will need to look up the CREATE TABLE syntax to get it right. You may also want to use .schema movies for ideas)
    • id (auto generated),
    • targetid, an integer which references an entry in the users table
    • sourceid, an integer which references an entry in the users table
    • comment, a string.

19. In the database console, verify that you created the table correctly with .schema.

20. In the database console, generate four sample pieces of feedback. for at least two different users.

21. In the database console, try the command SELECT * from users,feedback to see what you get.

22. Figure out how to select just the feedback for one user.

23. Make a backup of the new db/development.sqlite3.

24. Restore the original db/development.sqlite3.

Debrief

What do I expect you to take away from all of this?

• You can master the basics of SQL in about two hours.
• You will, however, miss some subtleties, such as how to set up tables that automatically generate and check keys.
• Some of the commands are really powerful and therefore really time consuming.
  • It takes a bit of time to understand the subtleties of join
  • But there are lots of good articles
• Spend three (eight hour) days of your life learning this stuff. It’s valuable.
• When you are working in rails, it’s all done for you.