-- This Entrance script colors the US States based on
-- the elections results reported by the NY Times
-- web site:  http://nytimes.com/
--
-- More about Entrance at http://dbentrance.com/

-- drop table if exists election_projection;

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

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

PLOT USColoringBook
  LAYER, COLOR OVERRIDE
 WITH
  COMMENT "Obama Wins!"

  -- maintain the frame aspect ratio 500:375
  FRAME 100 0 600 375
  PAGE  0  0   1200 375

  BACKGROUND gray
  FOREGROUND white 
  LEGEND at 600 150

  scarletred  LEGENDITEM       "McCain State"
  skyblue     LEGENDITEM       "Obama State"
  light scarletred  LEGENDITEM "McCain Leading"
  light skyblue    LEGENDITEM  "Obama Leading"
  no outlines
  font "sans serif" plain 18

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

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


union
select  "states_outline", "white";

report 
  title "    Redrawn using Entrance and data from the NY Times Web Site, November 5, 2008, http://nytimes.com/"
  title " "
  background gray
  foreground white
  PAGE  0  0   1200 600
select '';