
---
--- Testing a CD database with some foreign keys.
--- This sets up a many-many relationship between 
--- CD's and Songs with Tracks as the intermediary.
---
--- To load this into a SQLite database :
---   $ sqlite3 testcd.db < testcd.sql
---
--- To look at it from the command line :
---   $ sqlite3 textcd.db
---   sqlite> .tables
---   sqlite> .schema cd
---   sqlite> SELECT * FROM track;
---   sqlite> SELECT cd.title, song.name, track.number 
---      ...> FROM cd, song, track 
---      ...> WHERE cd.id=track.cd AND song.id=track.song AND 
---      ...> song.name like "%Queen%";
---
--- For discussion :
---   What does that last query look like using JOIN ?
---   And what sort of JOIN would be appropriate?
---   Which are the foreign keys?
---   Look into "ON DELETE CASCADE" and "ON UPDATE CASCADE": what and why?
---
--- See :
---   http://www.sqlite.org/sqlite.html       command line "dot" specials
---   http://www.sqlite.org/lang.html         SQL syntax understood by sqlite
---   http://en.wikipedia.org/wiki/Join_(SQL)
---
--- Coming :
---   What does this look like using an ORM
---
--- 
---   +-----------------+ 
---   |  cd             |
---   |-----------------|
---   |  cdid           |
---   |  title          |
---   +-----------------+
---            |
---            |
---           /|\
---   +-----------------+
---   |  track          |
---   |-----------------|
---   |  cdid           |
---   |  songid         |
---   |  number         |
---   +-----------------+
---           \|/
---            |
---            |
---      +----------+
---      |  song    |
---      |----------|
---      |  songid  |
---      |  name    |
---      +----------+
---
---

--- Enable foreign key syntax (sqlite specific)
PRAGMA foreign_keys = ON;

--- Remove all the tables, so we can start fresh.

--- Define them

---   UNIQUE KEY title (title),

CREATE TABLE cd (
  id            INTEGER PRIMARY KEY,
  title         TEXT
);


CREATE TABLE song (
  id            INTEGER PRIMARY KEY,
  name          TEXT NOT NULL
);

CREATE TABLE track (
  id            INTEGER PRIMARY KEY,
  number        INTEGER,
  song          INTEGER REFERENCES song(id),
  cd            INTEGER REFERENCES cd(id)
);

--- Populate them.

INSERT INTO cd VALUES (1,'Waltzing at the Grange');
INSERT INTO cd VALUES (2,'Celtic Groove Brand');

INSERT INTO song VALUES (1, 'The Forlorn Queen');
INSERT INTO song VALUES (2, 'A Random Waltz');
INSERT INTO song VALUES (3, 'A Random Groove');

--- Forlorn Queen (song 1) is on both CD's
INSERT INTO track VALUES (1, 10, 1, 1);
INSERT INTO track VALUES (2, 20, 1, 2);

--- Random Waltz is only on waltz album
INSERT INTO track VALUES (3, 30, 2, 1);

--- Random Groove is only on groove album.
INSERT INTO track VALUES (4, 30, 3, 2);
