#!/usr/bin/env ruby

require 'msql'

# referenced: http://www.shakespeare-online.com/

plays = [
  ["Henry VI, Part II",           "Histories", 1590, 1594],
  ["Henry VI, Part III",          "Histories", 1590, 1594],
  ["Henry VI, Part I",            "Histories", 1591, 1623],
  ["Richard III",                 "Histories", 1592, 1597],
  ["Comedy of Errors",            "Comedies",  1592, 1623],
  ["Titus Andronicus",            "Tragedies", 1593, 1594],
  ["The Taming of the Shrew",     "Comedies",  1593, 1594],
  ["The Two Gentlemen of Verona", "Comedies",  1594, 1623],
  ["Love's Labour's Lost",        "Comedies",  1594, 1598],
  ["Romeo and Juliet",            "Tragedies", 1595, 1597],
  ["Richard II",                  "Histories", 1595, 1597],
  ["A Midsummer Night's Dream",   "Comedies",  1595, 1600],
  ["King John",                   "Histories", 1594, 1623],
  ["The Merchant of Venice",      "Comedies",  1596, 1600],
  ["Henry IV, Part I",            "Histories", 1597, 1598],
  ["Henry IV, Part II",           "Histories", 1597, 1600],
  ["Much Ado About Nothing",      "Comedies",  1598, 1600],
  ["Henry V",                     "Histories", 1598, 1600],
  ["Julius Caesar",               "Tragedies", 1599, 1623],
  ["As You Like It",              "Comedies",  1599, 1623],
  ["Twelfth Night",               "Comedies",  1599, 1623],
  ["Hamlet",                      "Tragedies", 1600, 1603],
  ["The Merry Wives of Windsor",  "Comedies",  1600, 1602],
  ["Troilus and Cressida",        "Tragedies", 1601, 1609],
  ["All's Well That Ends Well",   "Comedies",  1602, 1623],
  ["Measure for Measure",         "Comedies",  1604, 1623],
  ["Othello",                     "Tragedies", 1604, 1622],
  ["King Lear",                   "Tragedies", 1605, 1608],
  ["Macbeth",                     "Tragedies", 1605, 1623],
  ["Antony and Cleopatra",        "Tragedies", 1606, 1623],
  ["Coriolanus",                  "Tragedies", 1607, 1623],
  ["Timon of Athens",             "Tragedies", 1607, 1623],
  ["Pericles",                    "Comedies",  1608, 1609],
  ["Cymbeline",                   "Comedies",  1609, 1623],
  ["The Winter's Tale",           "Comedies",  1610, 1623],
  ["The Tempest",                 "Comedies",  1611, 1623],
  ["Henry VIII",                  "Histories", 1612, 1623]
]

msql_client = Msql.connect

msql_client.create_db 'Shakespeare'
msql_client.select_db 'Shakespeare'


msql_client.query "CREATE TABLE plays ( name char(30), genre char(10), performed_year int, printed_year int)"

plays.each do |play|
  name, genre, performed_year, printed_year = play
  name.tr!('\'', '\\\'')
  msql_client.query "INSERT INTO plays VALUES ('#{name}', '#{genre}', #{performed_year}, #{printed_year})"
end


msql_client.query "SELECT * FROM plays WHERE genre = 'Tragedies'"

result = msql_client.store_result
result.each_row do |row|
  row.each{|elt| print "#{elt}\t"}
  print "\n"
end