(In response to by Robert Klemme)
Please Hugh, post the script or at least parts of it. I have been doing
lots of database filling recently and might be able to give you a few
pointers. Here's the general ones:
I don't think it is particularly pretty. TableMaker used to
generate SQL directly, now it uses AR instead, so the output file is
unused. I've tried to clear out the other unused stuff that is no
use to you but I may still need.
Thank you
Hugh
#!/usr/local/bin/ruby -w
$: << '/home/hgs/aeg_intranet/csestore/app/models'
# require 'csv'
require 'set'
require 'open-uri'
require 'net/http'
require 'date'
require 'md5'
# require 'hashattr'
require 'fasthashequals'
require "rubygems"
require_gem "activerecord" # for the ORM.
# Makes no sense to include these before active_record
# These are just (almost empty) models from rails. There are some
# relationship definitions (has_a, etc) but that's about it.
require 'student'
require 'cse_module'
require 'device'
$debug = false
# Class for creating the database tables from the supplied input
class TableMaker
attr_accessor :students, :cse_modules
INPUT = "hugh.csv"
OUTPUT = "populate_tables.sql"
ACCEPTED_MODULES = /^\"TECH(100[1-7]|200\d|201[01]|300\d|301[0-2])|MUST100[28]/
STRFTIME_FORMAT = "%a, %d %b %Y %H:%M:%S GMT"
PATH_TO_IMAGES = 'Z:\\new\\jpegs\\'
# Read in the database and populate the tables.
def initialize(input=INPUT, output=OUTPUT)
begin
puts "TableMaker.initialize (input=#{input.inspect}, output=#{output.inspect}"
# check these agree
# Struct.new( "Student", :forename, :surname, :birth_dt,
#
icture, :coll_status)
# Struct.new("Ident", :student,
number)
# Struct.new("CourseModule", :aos_code, :dept_code,
# :aos_type, :full_desc)
# Struct.new("StudentModule", :student_id, :course_module)
@students = Set.new()
@cse_modules = Set.new()
@student_modules = Hash.new{Set.new()}
# Most images will be written in bulk so cache them
@web_timestamps = Hash.new()
# Initialize variables
forename, surname, birth_dt, pnumber, aos_code,
acad_period, stage_ind, dept_code, stage_code, aos_type,
picture, coll_status, full_desc = [nil] * 13
student, cse_module, ident = nil, nil, nil
record = nil
last_pnumber, last_aos_code = nil, nil
last_student, last_cse_module = nil, nil
open(input, 'r') do |infp|
while record = infp.gets
# record.strip!
puts "record is #{record}" if $debug
# Don't split off the rest till we need it.
# Hopefully splitting on strings is faster.
forename, surname, birth_dt,
pnumber, aos_code, the_rest = record.split(/\s*\|\s*/,6)
next unless aos_code =~ ACCEPTED_MODULES
forename, surname, birth_dt, pnumber, aos_code,
acad_period, stage_ind, dept_code, stage_code, aos_type,
picture, coll_status, full_desc = record.split(/\s*\|\s*/)
puts "from record, picture is [#{picture.inspect}]." if $debug
if pnumber == last_pnumber
student = last_student
puts "pnumber set to last_pnumber" if $debug
else
# Structures for student
student = Student.new(
:forename => forename,
:surname => surname,
:birth_dt => birth_dt,
number => pnumber,
icture => picture,
:coll_status => coll_status
)
# Avoid duplicates
# unless @students.include? student
@students.add student
# else
# puts "Already seen #{student}" if $debug
# end
last_pnumber = pnumber
last_student = student
end
# Structures for module data.
if aos_code == last_aos_code
this_cse_module = last_cse_module
else
this_cse_module = CseModule.new(
:aos_code => aos_code,
:dept_code => dept_code,
:aos_type => aos_type,
:full_desc => full_desc
)
end
# Avoid duplicates
@cse_modules.add this_cse_module
last_cse_module = this_cse_module
@student_modules[student].add this_cse_module
puts "cse_module is #{this_cse_module}" if $debug
end
end
rescue
puts "\n"
puts $!
puts $!.backtrace.join("\n")
end
end
def has_student?(given_student)
result = @students.member?(given_student)
puts "has_student?: @students.size is #{@students.size}, result is #{result}"
return result
end
def diff_students(other_table)
diff_students = @students - other_table.students
return Set.new(diff_students)
end
# The pnumber is a barcode that uniquely identifies a student.
def has_pnumber?(apnumber)
return @students.any? do |pn|
pn == apnumber
end
end
def new_pnumber(old_table)
new_pnumbers = @pnumbers.reject do |pn|
old_table.has_pnumber?(pn)
end
return Set.new(new_pnumbers)
end
# Convert the picture to a URI and get it, if necessary.
# moved out of make_cards to shorten that function.
def get_picture(pic_name)
pic = "#{pic_name}"
pic.gsub!(/\"/,'')
pic.gsub!(/ /, "%20")
url = pic.dup
puts "pic is #{pic.inspect}\nurl is #{url.inspect}" # if $debug
pic.sub!(/^.*\//,'')
puts "pic is now #{pic.inspect}" # if $debug
if pic.empty?
puts "No such picture " if $debug
elsif pic =~ /^Z:\\/i
puts "Already got this " if $debug
else
Dir.chdir("./images") do
begin
grab = true
url =~ /^http:\/\/([^:\/]+):?([^\/]*?)(.*)/
host, port, path = $1, $2, $3
port = 80 if port.nil? or port.empty?
puts "pic #{pic}:- host #{host} port #{port} path #{path} " #if $debug
Net::HTTP.start(host, port) do |http|
header = http.head(path)
lastmod = header['last-modified']
# timestamp = DateTime.strptime(lastmod, STRFTIME_FORMAT)
# timestamp = Time.new(DateTime.strptime(lastmod, STRFTIME_FORMAT))
lastmod ||=Time.now.to_s
timestamp = (@web_timestamps[lastmod] ||= Time.parse(lastmod))
if File.exist?(pic)
mtime = File.mtime(pic)
puts "mtime #{mtime} timestamp #{timestamp}" if $debug
if mtime > timestamp
puts "file is newer, skip." if $debug
grab = false
end
end
if grab
open(pic, "wb") do |image|
image.print http.get(path).body
end
end
end
rescue => e
puts e.inspect
puts "\n"
puts "#{$!}, #{e}"
puts $!.backtrace().join("\n")
end
end
end
return PATH_TO_IMAGES + pic + "\r\n"
end
# Output all the data necessary to create the id cards.
def make_cards(output,the_students = @students)
personal_fields = [:forename, :surname, :birth_dt,
number]
open(output, "w") do |outf|
the_students.each do |student|
puts "student:- #{student} :" if $debug
outstring = personal_fields.collect do |message|
# Remove unwanted quotation marks
"#{student.send(message)}, ".gsub(/"/,'')
end.join('')
# We need to iterate in case a student has two ids
# Not any more -- we know they will look like two students.
# It doesn't matter.
outstring += get_picture(student.picture)
outf.print outstring
end
end
end
# Cannot update the database til the comparison is complete, so
# this code must be moved into here
def update_database
@students.each do |student|
puts "update_database(): pnumber is #{student.pnumber}"
begin
orig_student = Student.find
first, :conditions => ["pnumber = ?",student.pnumber])
puts "update_database(): orig_student.pnumber is #{orig_student.pnumber}"
rescue Exception => e
puts "update_database(): exception is #{e}"
puts "\n"
puts $!
puts $!.backtrace.join("\n")
puts "\n"
orig_student = nil
end
if orig_student.nil? # i.e. nothing found
student.save!
else
orig_student.update_attributes(
:surname => student.surname,
:birth_dt => student.birth_dt,
icture => student.picture,
:coll_status => student.coll_status
)
end
end
@cse_modules.each do |cse_module|
orig_cse_module = CseModule.find
first, :conditions => ['aos_code = ?', cse_module.aos_code]) rescue nil
if orig_cse_module.nil?
cse_module.save!
else
orig_cse_module.update_attributes(
:dept_code => cse_module.dept_code,
:aos_type => cse_module.aos_type,
:full_desc => cse_module.full_desc
)
end
end
# This next line should sort out the join table.
@student_modules.each do |student, modules|
the_student = Student.find
first, :conditions => ['pnumber = ?', student.pnumber])
modules.each do |cse_module|
the_cse_module = CseModule.find
first, :conditons => ['aos_code = ?', cse_module.aos_code])
puts "update_database(): updating #{the_cse_module} with #{the_student}"
the_cse_module.students << the_student
end
end
end
end
class KitTableMaker
def initialize(input)
# create outside the block for speed.
name, serialno, barcode = [nil]*3
@kit = Set.new()
barcodes = Set.new()
open(input, 'r') do |infp|
while record = infp.gets
name, serialno, barcode = record.split(/\s*,\s*/,3)
if barcodes.member?(barcode)
puts "Duplicate barcode #{barcode}"
else
device = Device.new
description => name,
:serialno => serialno,
:barcode => barcode)
barcodes.add(barcode)
@kit.add device
end
end
end
end
def update_database
@kit.each do |device|
begin
orig_kit = Device.find
first, :conditions => ["barcode = ?", device.barcode])
rescue Exception => e
puts "Device::update_database: exception is #{e}"
puts "\n", $!, $!.backtrace.join("\n"), "\n"
end
if orig_kit.nil?
device.save!
else
begin
orig_kit.update_attributes
description => device.name,
:serialno => device.serialno,
:barcode => device.barcode)
rescue Exception => e
puts "Device::update_database: exception is #{e}"
puts "\n", $!, $!.backtrace.join("\n"), "\n"
end
end
end
end
end
if __FILE__ == $0
begin
ActiveRecord::Base.establish_connection(
:adapter => 'mysql',
:host => 'localhost',
ort => 3608,
:database => 'csestore_development',
:username => 'hgs',
assword => 'post-it-to-ruby-talk?'
)
new_table = TableMaker.new("hugh.csv", "update_tables.sql")
new_table.update_database()
old_table = TableMaker.new("hugh.csv.old")
new_table.make_cards("cards.out")
new_table.make_cards("new_cards.out", new_table.diff_students(old_table))
rescue Exception => e
puts "\n"
puts "#{$!}, #{e}"
puts $!.backtrace().join("\n")
end
device_table = KitTableMaker.new("stock1.csv")
device_table.update_database()
end
