-- This is an Entrance example, http://dbentrance.com/

-- This script colors the US States based on
-- whether the NY Times said they were rated 
--    strong mccain
--    leaning mccain
--    tossup
--    leaning obama
--    strong obama
-- on August 30, 2008 on their web site:
-- http://elections.nytimes.com/2008/president/whos-ahead/key-states/map.html"

-- drop table if exists election_projection;

create table election_projection (
    name varchar(255),
    who  varchar(255)
);

insert into election_projection
 values 
 ('Alabama','tossup'),                      
 ('Alaska','strong mccain'),                
 ('Arizona','strong mccain'),               
 ('Arkansas','strong mccain'),              
 ('California','strong obama'),             
 ('Colorado','tossup'),                     
 ('Connecticut','tossup'),                  
 ('Delaware','strong obama'),               
 ('District of Columbia','strong obama'),   
 ('Florida','leaning mccain'),              
 ('Georgia','leaning mccain'),              
 ('Hawaii','strong obama'),                 
 ('Idaho','strong mccain'),                 
 ('Illinois','strong obama'),               
 ('Indiana','tossup'),                      
 ('Iowa','leaning obama'),                  
 ('Kansas','strong mccain'),                
 ('Kentucky','strong mccain'),              
 ('Louisiana','tossup'),                    
 ('Maine','strong obama'),                  
 ('Maryland','strong obama'),               
 ('Massachusetts','strong obama'),          
 ('Michigan','tossup'),                     
 ('Minnesota','leaning obama'),             
 ('Mississippi','strong mccain'),           
 ('Missouri','leaning mccain'),             
 ('Montana','leaning mccain'),              
 ('Nebraska','strong mccain'),              
 ('Nevada','tossup'),                       
 ('New Hampshire','tossup'),                
 ('New Jersey','tossup'),                   
 ('New Mexico','tossup'),                   
 ('New York','strong obama'),               
 ('North Carolina','tossup'),               
 ('North Dakota','leaning mccain'),         
 ('Ohio','tossup'),                         
 ('Oklahoma','strong mccain'),              
 ('Oregon','leaning obama'),                
 ('Pennsylvania','leaning obama'),          
 ('Rhode Island','tossup'),                 
 ('South Carolina','strong mccain'),        
 ('South Dakota','tossup'),                 
 ('Tennessee','strong mccain'),             
 ('Texas','strong mccain'),                 
 ('Utah','strong mccain'),                  
 ('Vermont','strong obama'),                
 ('Virginia','leaning obama'),              
 ('Washington','leaning obama'),            
 ('West Virginia','tossup'),                
 ('Wisconsin','tossup'),                    
 ('Wyoming','tossup')
;                 

PLOT USColoringBook
  LAYER, COLOR OVERRIDE
 WITH
  TRANSLUCENCE 255
  NO SIDES
  NO XAXIS
  NO YAXIS
  NO OUTLINE

  FRAME 100 0 600 375
  PAGE  0  0   1200 375

  BACKGROUND gray
  FOREGROUND white 

  LEGEND at 600 150
  dark scarletred  LEGENDITEM "Strong McCain"
  light scarletred LEGENDITEM "Leaning McCain"
  dark skyblue     LEGENDITEM "Strong Obama"
  light skyblue    LEGENDITEM "Leaning Obama"
  light butter     LEGENDITEM "Tossup"
  
  font "sans serif" plain 18

select
    name, 
    concat(
      if(who like 'leaning%', 'light', 'dark'),
      ' ',
      if(who like '%mccain',   'scarletred', 'skyblue'))
  from election_projection
    where who not like 'tossup'

union
select
     name,
    'light butter' 
   from election_projection
     where who like 'tossup'


union
select  "states_outline", "white";

report 
  title "    Redrawn using Entrance based on data from:"
  title " "
  title "        NY Times 'The Electoral Map', August 30, 2008"
  title "        http://elections.nytimes.com/2008/president/whos-ahead/key-states/map.html"
  title " "
  background gray
  foreground white
  PAGE  0  0   1200 600
select '';