require 'rubygems'
require 'mysql'
require 'date'

origin = Date.parse('2006-01-01')

db = Mysql::new('localhost', 'rich')

begin
  db.query(%Q{select 1 from sites.dates})
rescue Mysql::Error
  create_ddl = <<-SQL
create table sites.dates (
  sql_date          date not null,
  cal_year          int not null,
  cal_month         int not null,
  cal_week          int not null,
  cal_day_in_month  int not null,
  cal_day_in_year   int not null,
  cal_day_in_week   int not null,
  cal_year_month    varchar(10) not null
);
SQL
  db.query(create_ddl)
end

db.query('begin')
0.upto(5000) do |i|
  d = (origin + i).to_s
  sql = <<-SQL
  insert into sites.dates (sql_date, cal_year, cal_month, cal_week, cal_day_in_month, cal_day_in_year, cal_day_in_week, cal_year_month) values ('#{d}', extract(year from '#{d}'), extract(month from '#{d}'), extract(week from '#{d}'), extract(day from '#{d}'), dayofmonth('#{d}'), dayofweek('#{d}'), extract(year_month from '#{d}'));
SQL
  db.query(sql)
end
db.query('commit')