#!/usr/bin/ruby

require 'rubygems'
require 'sqlite3'

class Loader

  def initialize file
    @file = file
    if File.file?(@file) then
      @db = SQLite3::Database.new('blog.sqlite') 
    else
      $stderr.puts "#{file}: ignored"
      @db = nil
    end
  end

  def close
    @db.close
  end

  def mkdb
    @db.execute_batch(<<-ENDSQL)
      CREATE TABLE IF NOT EXISTS blog(
        ttaaii	STRING(6) NOT NULL,
	cccc	STRING(4) NOT NULL,
	cntname	TEXT NOT NULL,
	categ	TEXT NOT NULL,
	CONSTRAINT ahl UNIQUE (ttaaii, cccc)
      );
    ENDSQL
  end

  def storerow row
    begin
      @db.execute(<<-ENDSQL, row)
	REPLACE INTO blog(ttaaii, cccc, cntname, categ)
	VALUES (:ttaaii, :cccc, :cntname, :categ)
      ENDSQL
    rescue Exception => e
      p row
      raise e
    end
  end

  def convline w
    row = {}
    row[:categ] = w[0]
    row[:cntname] = w[1]
    raise "ahl #{w[2]} malformatted" unless /^(\w{6}) (\w{4})$/ =~ w[2]
    row[:ttaaii] = $1
    row[:cccc] = $2
    row
  end

  def convfile
    @db.transaction do
      fp = File.open(@file)
      fp.gets
      for line in fp
        row = convline(line.sub(/\r?\n/, '').split(/,/))
        storerow row 
      end
    end
  end

  def run
    return if @db.nil?
    mkdb
    convfile
    close
  end

end

for file in ARGV
  Loader.new(file).run
end