+
+ 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 descr FROM Series WHERE id=$1;"
+ args = [id]
+ @conn.exec_params(sql, args) do |rs|
+ if rs.ntuples > 0
+ return rs[0]['descr']
+ end
+ end
+ return nil
+ 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
+ begin
+ # DEBUG: puts 'SQL> ' + sqlInsert + ': ' + args.inspect()
+ rs = @conn.exec_params(sqlInsert, args)
+ rescue Exception => e
+ puts sqlInsert + ": " + args.inspect()
+ puts e.message
+ puts $@
+ ensure
+ rs.clear if rs
+ end
+ 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
+ book_ids = []
+ @conn.exec_params(sql, [pattern]) do |rs|
+ rs.each do |row|
+ book_ids.push(row['id'])
+ end
+ end
+ return book_ids
+ end