+
+ def exec_id_query(sql, args)
+ ids = []
+ @conn.exec_params(sql, args) do |rs|
+ rs.each do |row|
+ ids.push(row['id'])
+ end
+ end
+ return ids
+ end
+
+ def exec_update(sql, args)
+ begin
+ rs = @conn.exec_params(sql, args)
+ rescue Exception => e
+ puts sql + ": " + args.inspect()
+ puts e.message
+ puts $@
+ ensure
+ rs.clear if rs
+ end
+ end
+
+ def next_id(seq_name)
+ id = nil
+ @conn.exec("SELECT nextval('" + seq_name + "');") do |rs|
+ id = rs[0]['nextval']
+ end
+ return id
+ end
+
+ def get_series(grouping, code)
+ if nil == code
+ return nil
+ end
+
+ sql = "SELECT id FROM Series WHERE grouping=$1 AND code=$2;"
+ args = [grouping, code]
+ @conn.exec_params(sql, args).each do |row|
+ return row['id']
+ end
+
+ # TODO: Create a new series object here?
+ puts 'WARNING: series("' + grouping + '", "' + code + '") not found.'
+ return nil
+ end
+
+ def load_series(id)
+ sql = "SELECT age,genre,grouping,code,descr FROM Series WHERE id=$1;"
+ args = [id]
+ @conn.exec_params(sql, args) do |rs|
+ if rs.ntuples > 0
+ row = rs[0]
+ series = Series.new(id)
+ series.age = row['age']
+ series.genre = row['genre']
+ series.grouping = row['grouping']
+ series.code = row['code']
+ series.descr = row['descr']
+ return series
+ end
+ end
+ return nil
+ end
+
+ def populate_classifications_table
+ puts "Populating the Classifications table..."
+ first = true
+ CSV.foreach(@basePath + '/csv/class.csv') do |row|
+ if first
+ # skip the header row
+ first = false
+ else
+
+ # First, add a row to the Classifications table
+
+ id = next_id('classification_id')
+ ddc = row[0]
+ lcc = row[1]
+ author_grouping = row[2]
+ author_sort = row[3]
+ title_grouping = row[4]
+ title = row[5]
+
+ sqlInsert = "INSERT INTO Classifications (id, ddc, lcc, author_grouping, author_sort, title_grouping, title) VALUES ($1, $2, $3, $4, $5, $6, $7);"
+ args = [id, ddc, lcc, author_grouping, author_sort, title_grouping, title]
+ exec_update(sqlInsert, args)
+
+ # Second, link up with the appropriate FAST table entries
+
+ fast = []
+ input = row[6]
+ if input.length > 0
+ fast = input.split(';')
+ end
+
+ fast.each do |fast_id|
+ sqlInsert = "INSERT INTO FAST_Classifications (fast, classification) VALUES ($1, $2);"
+ args = [fast_id, id]
+ exec_update(sqlInsert, args)
+ end
+ end
+ end
+ end
+
+ def populate_fast_table
+ puts "Populating the FAST table..."
+ first = true
+ CSV.foreach(@basePath + '/csv/fast.csv') do |row|
+ if first
+ first = false # skip the header row
+ else
+ id = row[0]
+ descr = row[1]
+ sqlInsert = "INSERT INTO FAST (id, descr) VALUES ($1, $2);"
+ exec_update(sqlInsert, [id, descr])
+ end
+ end
+ end
+
+ def populate_lists_table
+ puts "Populating the Lists table..."
+
+ CSV.foreach(@basePath + "/csv/lists.csv", headers: true) do |row|
+ author_ids = find_all_authors(row['author'])
+ if author_ids.empty?
+ specification = [row['age'], row['category'], row['code'], row['year'], row['author'], row['title']]
+ .map { |x| x.inspect }
+ .join(', ')
+
+ puts "WARNING: For list entry (#{specification}), no such author was found."
+
+ next
+ end
+
+ sqlInsert = %Q(
+ INSERT INTO Lists (id, age, category, code, year, author, title)
+ VALUES ($1, $2, $3, $4, $5, $6, $7);
+ )
+ author_ids.each do |author_id|
+ list_id = next_id('list_id')
+ args = [list_id, row['age'], row['category'], row['code'], row['year'], author_id, row['title']]
+ exec_update(sqlInsert, args)
+
+ update_lists_books_table(list_id, author_id, row['title'])
+ end
+ end
+ end
+
+ # Scan for books that match this Lists entry, and add any matches to the Lists_Books associative table
+ def update_lists_books_table(list_id, author_id, title)
+ title_pattern = Book.grouping_for_title(title).gsub('_', '%')
+ sqlSelect = "SELECT id FROM Books WHERE author = $1 AND title LIKE $2;"
+ args = [author_id, title_pattern]
+
+ @conn.exec_params(sqlSelect, args) do |rs|
+ rs.each do |row|
+ sqlInsert = "INSERT INTO Lists_Books (list, book) VALUES ($1, $2)"
+ args = [list_id, row['id']]
+ exec_update(sqlInsert, args)
+ end
+ end
+ end
+
+ def populate_series_table
+ puts "Populating the Series table..."
+ CSV.foreach(@basePath + '/csv/series.csv') do |row|
+ id = next_id('series_id')
+ sqlInsert = "INSERT INTO Series (id, age, genre, grouping, code, descr) VALUES ($1, $2, $3, $4, $5, $6);"
+ args = [id] + row
+ exec_update(sqlInsert, args)
+ end
+ end
+
+ def query_books_by_author(pattern)
+ sql =
+<<EOS
+ SELECT b.id FROM Authors a
+ INNER JOIN Books b ON b.author=a.id
+ LEFT OUTER JOIN Series s on s.id=b.series
+ WHERE upper(a.grouping) LIKE $1
+ ORDER BY a.grouping, b.series, b.volume, b.title
+EOS
+ return exec_id_query(sql, [pattern])
+ end
+
+ def query_books_by_ddc
+ sql =
+<<EOS
+ SELECT b.id FROM Classifications c
+ INNER JOIN Books b ON b.classification=c.id
+ ORDER BY c.ddc
+EOS
+ return exec_id_query(sql, [])
+ end
+
+ def query_books_by_series_id(id)
+ sql =
+<<EOS
+ SELECT b.id FROM Books b
+ WHERE b.series = $1
+ ORDER BY b.volume,b.title
+EOS
+ return exec_id_query(sql, [id])
+ end
+
+ def query_series_by_age(pattern)
+ sql =
+<<EOS
+ SELECT s.id
+ FROM Series s
+ WHERE s.age LIKE $1
+ ORDER BY s.grouping,s.descr
+EOS
+ return exec_id_query(sql, [pattern])
+ end