Exercise 13: Migrating And Evolving Data

This exercise will have you apply some skills you've learned. I'll have you take your database and "evolve" the schema to a different form. You'll need to make sure you know the previous exercise well and have your code.sql working as we'll be using it during this exercise. If you don't have either of these then go back and get everything straightened out.

For this exercise I'll show you another way to load your code.sql and begin working with the database using the -init option to sqlite3:

View Source file ex13.sh-session Only

$ sqlite3 -init code.sql ex13.db
-- Loading resources from code.sql
0|Zed|Shaw|37
Fluffy|1000
Gigantor|1
Fluffy|1000
0|Fluffy|1000|0
1|Gigantor|1|1
Gigantor|1
0|Fluffy|Unicorn|1000|0
0|Fluffy|Unicorn|1000|0
1|Gigantor|Robot|1|0

SQLite version 3.13.0 2016-05-18 10:57:30
Enter ".help" for usage hints.
sqlite> .schema
CREATE TABLE person (
    id INTEGER PRIMARY KEY,
    first_name TEXT,
    last_name TEXT,
    age INTEGER
);
CREATE TABLE pet (
    id INTEGER PRIMARY KEY,
    name TEXT,
    breed TEXT,
    age INTEGER,
    dead INTEGER
);
CREATE TABLE person_pet (
    person_id INTEGER,
    pet_id INTEGER
);
sqlite>

Notice how I first load the code.sql file and it drops me into the sqlite3 interactive prompt. This is where you can now work with the database live. I then type .schema to make sure I know how the database looks.