---------------------------------------------------------------
-- buildSpy.sql
-- builds and populates all databases for spy examples
-- uses SQLite - should adapt easily to other rdbms
-- by Andy Harris for PHP/MySQL for Abs. Beg
---------------------------------------------------------------


---------------------------------------------------------------
-- conventions
---------------------------------------------------------------

-- primary key = table name . ID
-- primary key always first fields
-- all primary keys autonumbered
-- all field names camel-cased
-- only link tables use underscore
-- foreign keys indicated although mySQL does not enforce
-- every table used as foriegn reference has a name field
---------------------------------------------------------------

---------------------------------------------------------------
--housekeeping
---------------------------------------------------------------


--DROP TABLE agent;
--DROP TABLE agent_specialty;
--DROP TABLE badspy;
--DROP TABLE operation;
--DROP TABLE specialty;

CREATE TABLE agent (
  agentID INTEGER PRIMARY KEY,
  name varchar(50),
  operationID int
); 

INSERT INTO agent VALUES (null, 'Bond', 1);
INSERT INTO agent VALUES (null, 'Falcon', 1);
INSERT INTO agent VALUES (null, 'Cardinal', 2);
INSERT INTO agent VALUES (null, 'Blackford', 2);
INSERT INTO agent VALUES (null, 'Rahab', 3);
INSERT INTO agent VALUES (null, 'James Bland', 2);

CREATE TABLE operation (
  operationID INTEGER PRIMARY KEY,
  name varchar(50),
  description varchar(50),
  location varchar(50)
);

INSERT INTO operation VALUES (null, 'Dancing Elephant', 'Infiltrate suspicious zoo', 'London');
INSERT INTO operation VALUES (null, 'Enduring Angst', 'Make bad guys feel really guilty', 'Lower Volta');
INSERT INTO operation VALUES (null, 'Furious Dandelion', 'Plant crabgrass in enemy lawns', 'East Java');

CREATE TABLE specialty (
  specialtyID INTEGER PRIMARY KEY,
  name varchar(50) 
);

INSERT INTO specialty VALUES (null, 'Electronics');
INSERT INTO specialty VALUES (null, 'Counterintelligence');
INSERT INTO specialty VALUES (null, 'Sabotage');
INSERT INTO specialty VALUES (null, 'Doily Design');
INSERT INTO specialty VALUES (null, 'Explosives');
INSERT INTO specialty VALUES (null, 'Flower Arranging');

CREATE TABLE agent_specialty (
  agent_specialtyID INTEGER PRIMARY KEY,
  agentID int,
  specialtyID int
);

INSERT INTO agent_specialty VALUES (null, 1, 2);
INSERT INTO agent_specialty VALUES (null, 1, 3);
INSERT INTO agent_specialty VALUES (null, 2, 1);
INSERT INTO agent_specialty VALUES (null, 2, 6);
INSERT INTO agent_specialty VALUES (null, 3, 2);
INSERT INTO agent_specialty VALUES (null, 4, 4);
INSERT INTO agent_specialty VALUES (null, 4, 5);

CREATE TABLE badspy (
  agentID INTEGER PRIMARY KEY,
  name varchar(30),
  specialty varchar(40),
  assignment varchar(40),
  description varchar(40),
  location varchar(20)
);

INSERT INTO badspy VALUES (null, 'Rahab', 'Electronics, Counterintelligence', 'Raging Dandelion', 'Plant Crabgrass', 'Sudan');
INSERT INTO badspy VALUES (null, 'Gold Elbow', 'Sabatoge, Doily design', 'Dancing Elephant', 'Infiltrate suspicious zoo', 'London');
INSERT INTO badspy VALUES (null, 'Falcon', 'Counterintelligence', 'Dancing Elephant', 'Infiltrate suspicious circus', 'London');
INSERT INTO badspy VALUES (null, 'Cardinal', 'Sabatoge', 'Enduring Angst', 'Make bad guys feel really guilty', 'Lower Volta');
INSERT INTO badspy VALUES (null, 'Blackford', 'Explosives, Flower arranging', 'Enduring Angst', 'Make bad guys feel really guilty', 'Lower Votla');