webrickとAccess操作のサンプル

server.rb

# coding: utf-8
require 'webrick'
require 'JSON'
require 'win32ole'

BASE_DIR = File.dirname(__FILE__)
FIELDS = ["ID","title","url","borrower","borrow_start","borrow_end"]
DB_PATH = 'Books.mdb'
CONFIG = JSON.parse(File.read(BASE_DIR + "/config.json").sub(/var conf = /, ''))

class BookServer
  def start
    document_root = BASE_DIR
    server = WEBrick::HTTPServer.new({
      :DocumentRoot => document_root,
      :BindAddress => '0.0.0.0',
      :Port => 3000
    })
    
    server.mount('/books', Books)
    server.mount('/borrow', Borrow)
    
    ['INT', 'TERM'].each {|signal|
      Signal.trap(signal){ server.shutdown }
    }
    
    server.start
  end
end

module Recordset
  def [] field
    self.Fields.Item(field).Value
  end

  def []= field,value
    self.Fields.Item(field).Value = value
  end
  
  def each_record
    if self.EOF or self.BOF
      return 
    end
    self.MoveFirst
    until self.EOF or self.BOF
      yield self
      self.MoveNext
    end
  end
end

class Books < WEBrick::HTTPServlet::AbstractServlet
  include WEBrick::HTMLUtils
  
  def do_GET(req, res)
    today = Time.now.strftime("%Y%m%d")
    DbUtil.update("UPDATE Book Set borrower = '', borrow_start = '', borrow_end = '' WHERE borrow_end < '#{today}';")
    list = DbUtil.select("SELECT * FROM Book;", FIELDS)
    res['Content-Type'] = 'text/html'
    callback = req.query['callback']
    res.body = "#{callback}(#{list.to_json})"
  end
  
end

class Borrow < WEBrick::HTTPServlet::AbstractServlet
  include WEBrick::HTMLUtils
  
  def do_GET(req, res)
    now_list = DbUtil.select("SELECT * FROM Book WHERE borrower = '#{CONFIG['user']}';", FIELDS)
    today = Time.now.strftime("%Y%m%d")
    enddate = req.query['enddate']
    enddate = today if enddate == ''
    ids = req.query['ids'].split(/,/).map{|i| i.to_i}
    
    ids.each do |id|
      if !now_list.any?{|book| book['ID'] == id}
        puts "borrow1 #{id}"
        DbUtil.update("UPDATE Book Set borrower = '#{CONFIG['user']}', borrow_start = '#{today}', borrow_end = '#{enddate}' WHERE ID = #{id};")
      end
    end
    now_list.each do |book|
      if !ids.include?(book['ID'])
        puts "borrow2 #{book['ID']}"
        DbUtil.update("UPDATE Book Set borrower = '', borrow_start = '', borrow_end = '' WHERE ID = #{book['ID']};")
      end
    end
    
    res['Content-Type'] = 'text/html'
    res.body = "[]"
  end
  
end

class DbUtil
  @@cn = nil
  
  def self.select(sql, fields)
    puts "select:#{sql}"
    list = []
    rs = @@cn.Execute(sql)
    rs.extend Recordset
    rs.each_record do |rs|
      map = {}
      values = fields.map do |field|
        map[field] = rs[field]
      end
      list << map
    end
    list
  end
  
  def self.update(sql)
    puts "update:#{sql}"
    rs = @@cn.Execute(sql)
  end
  
  def self.connect
    @@cn = WIN32OLE.new("ADODB.Connection")
    connstr = "DRIVER={Microsoft Access Driver (*.mdb)};Dbq=#{DB_PATH}"
    @@cn.Open connstr
  end
end

DbUtil.connect
server = BookServer.new
server.start