--- cd.sql
---
--- This is a demo of CD database with some foreign keys,
--- which sets up a many-to-many relationship between 
--- CD's and Songs with Tracks as the intermediary.
---
--- This .sql file contains sqlite3 commands. (See sqlite.org.)
---
--- The command line "sqlite3" tool and python library "import sqlite3"
--- are both installed on jupyter.bennington ; if you upload this file
--- there you can use it as described below.
---
--- ---------------------------------------------------------------
---
--- To create the database file cd.db by loading this file into sqlite :
---   $ sqlite3 cd.db < cd.sql
---
--- To look at the information in cd.db from the command line :
---   $ sqlite3 cd.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.
DROP TABLE IF EXISTS cd;
DROP TABLE IF EXISTS song;
DROP TABLE IF EXISTS track;

--- Define the tables.

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)
);

--- Put data into the tables.

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 (1, 2)
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);