Add support to cross-reference books against list of award-winners.

diff --git a/book.rb b/book.rb
index d7135690d4d60514f4c900d59dc76698d3eb164d..ea650249a9ac6c8c120e72824dce646276bc4c9f 100644 (file)
--- a/book.rb
+++ b/book.rb
@@ -10,6 +10,7 @@ require_relative 'store'
 
 class Book
   @@DC_NS_URL = 'http://purl.org/dc/elements/1.1/'
 
 class Book
   @@DC_NS_URL = 'http://purl.org/dc/elements/1.1/'

+  @@SERIES_AND_VOLUME_REGEX = /^([A-Z]+)([0-9]+(\.[0-9]+)?)$/

 
   attr_accessor :author
   attr_accessor :classification_id
 
   attr_accessor :author
   attr_accessor :classification_id


@@ -49,6 +50,18 @@ class Book
     return false
   end
 
     return false
   end
 

+  def self.grouping_for_title(title)
+    result = title
+
+    '\'",!#'.split('').each do |c|
+      result = result.gsub(c, '-')
+    end
+    result = result.gsub(/: */, '--')
+    result = result.gsub(' ', '_')
+
+    result
+  end
+

   def heading
     result = []
 
   def heading
     result = []
 


@@ -159,7 +172,7 @@ class Book
     vol = nil
 
     first = arr[0]
     vol = nil
 
     first = arr[0]

-    matchData = (arr[0]).match(/^([A-Z]+)([0-9]+)$/)
+    matchData = (arr[0]).match(@@SERIES_AND_VOLUME_REGEX)

     if nil != matchData
       capt = matchData.captures
       series = capt[0]
     if nil != matchData
       capt = matchData.captures
       series = capt[0]


@@ -174,6 +187,15 @@ class Book
 
     title = arr.join(' ')
 
 
     title = arr.join(' ')
 

+    bare_title_grouping = title_grouping
+      .split('_')
+      .reject { |part| part.match(@@SERIES_AND_VOLUME_REGEX) }
+      .join('_')
+
+    unless bare_title_grouping == Book.grouping_for_title(title)
+      puts "WARNING:  title_grouping mismatch:  #{bare_title_grouping.inspect} vs. #{Book.grouping_for_title(title).inspect}"
+    end
+

     return series, vol, title
   end
 
     return series, vol, title
   end
 

diff --git a/main.rb b/main.rb
index 564ba8b8159d523775146ca9324b3e96a1f0f83d..b0c4b795c714410162fc23c0953601b0fce12ab8 100644 (file)
--- a/main.rb
+++ b/main.rb
@@ -44,6 +44,8 @@ for arg in ARGV
   end
 end
 
   end
 end
 

+@store.cross_reference_lists
+

 puts 'Creating output...'
 
 navigator = Navigator.new(@store)
 puts 'Creating output...'
 
 navigator = Navigator.new(@store)

diff --git a/store.rb b/store.rb
index 5faf7ce884c21371a58ea3dbafec2cece7899a0a..f1b7fba68400465c102db20ba886e60c77d96207 100644 (file)
--- a/store.rb
+++ b/store.rb
@@ -48,8 +48,16 @@ class Store
     return path, name
   end
 
     return path, name
   end
 

+  def cross_reference_lists
+puts "@@@@@@@@@@@ CROSS-REF START @@@@@@@@@@@"
+    exec_update("TRUNCATE TABLE Lists CASCADE;", [])
+
+    populate_lists_table
+puts "@@@@@@@@@@@ CROSS-REF DONE @@@@@@@@@@@"
+  end
+

   def create_schema(skip_class)
   def create_schema(skip_class)

-    create_authors = 
+    create_authors =

 <<EOS
       CREATE TABLE Authors (
         id          INTEGER PRIMARY KEY,
 <<EOS
       CREATE TABLE Authors (
         id          INTEGER PRIMARY KEY,


@@ -59,7 +67,7 @@ class Store
       );
 EOS
 
       );
 EOS
 

-    create_books = 
+    create_books =

 <<EOS
       CREATE TABLE Books (
         id             INTEGER PRIMARY KEY,
 <<EOS
       CREATE TABLE Books (
         id             INTEGER PRIMARY KEY,


@@ -88,7 +96,7 @@ EOS
       );
 EOS
 
       );
 EOS
 

-    create_efs = 
+    create_efs =

 <<EOS
       CREATE TABLE EFS (
         id          INTEGER PRIMARY KEY,
 <<EOS
       CREATE TABLE EFS (
         id          INTEGER PRIMARY KEY,


@@ -96,7 +104,7 @@ EOS
       );
 EOS
 
       );
 EOS
 

-    create_fast = 
+    create_fast =

 <<EOS
       CREATE TABLE FAST (
         id          VARCHAR(32) PRIMARY KEY,
 <<EOS
       CREATE TABLE FAST (
         id          VARCHAR(32) PRIMARY KEY,


@@ -114,7 +122,30 @@ EOS
       );
 EOS
 
       );
 EOS
 

-    create_series = 
+    create_lists =
+<<EOS
+      CREATE TABLE Lists (
+        id             INTEGER PRIMARY KEY,
+        age            VARCHAR(32),
+        category       VARCHAR(32),
+        code           VARCHAR(2),
+        year           INTEGER,
+        author         INTEGER REFERENCES Authors(id),
+        title          VARCHAR(256)
+      );
+EOS
+
+    # Associative entity, linking Lists and Books tables
+    # in a 0..n to 0..m relationship
+    create_lists_books =
+<<EOS
+      CREATE TABLE Lists_Books (
+        list           INTEGER REFERENCES Lists(id),
+        book           INTEGER REFERENCES Books(id)
+      );
+EOS
+
+    create_series =

 <<EOS
       CREATE TABLE Series (
         id          INTEGER PRIMARY KEY,
 <<EOS
       CREATE TABLE Series (
         id          INTEGER PRIMARY KEY,


@@ -134,10 +165,13 @@ EOS
       create_series,
       create_books,
       create_fast_classifications,
       create_series,
       create_books,
       create_fast_classifications,

+      create_lists,
+      create_lists_books,

       'CREATE SEQUENCE author_id;',
       'CREATE SEQUENCE book_id;',
       'CREATE SEQUENCE classification_id;',
       'CREATE SEQUENCE efs_id;',
       'CREATE SEQUENCE author_id;',
       'CREATE SEQUENCE book_id;',
       'CREATE SEQUENCE classification_id;',
       'CREATE SEQUENCE efs_id;',

+      'CREATE SEQUENCE list_id;',

       'CREATE SEQUENCE series_id;'
     ]
 
       'CREATE SEQUENCE series_id;'
     ]
 


@@ -146,16 +180,17 @@ EOS
     end
 
     if skip_class == false
     end
 
     if skip_class == false

-      populate_fast_table()
-      populate_classifications_table()
+      populate_fast_table
+      populate_classifications_table

     end
 
     end
 

-    populate_series_table()
-
+    populate_series_table

   end
 
   def dropSchema
     stmts = [
   end
 
   def dropSchema
     stmts = [

+      'DROP TABLE Lists_Books;',
+      'DROP TABLE Lists;',

       'DROP TABLE Books;',
       'DROP TABLE FAST_Classifications;',
       'DROP TABLE Authors;',
       'DROP TABLE Books;',
       'DROP TABLE FAST_Classifications;',
       'DROP TABLE Authors;',


@@ -167,6 +202,7 @@ EOS
       'DROP SEQUENCE book_id;',
       'DROP SEQUENCE classification_id;',
       'DROP SEQUENCE efs_id;',
       'DROP SEQUENCE book_id;',
       'DROP SEQUENCE classification_id;',
       'DROP SEQUENCE efs_id;',

+      'DROP SEQUENCE list_id;',

       'DROP SEQUENCE series_id;'
     ]
 
       'DROP SEQUENCE series_id;'
     ]
 


@@ -179,14 +215,31 @@ EOS
     end
   end
 
     end
   end
 

+  def find_all_authors(author_name)
+    result = []
+
+    sqlSelect = "SELECT id FROM Authors WHERE grouping=$1;"
+    args = [author_name]
+
+    @conn.exec_params(sqlSelect, args) do |rs|
+      rs.each do |row|
+        result << row['id']
+      end
+    end
+
+    result
+  end
+

   def find_author(author)
     sqlSelect = "SELECT id FROM Authors WHERE grouping=$1 AND reading=$2 AND sort=$3;"
     args = [author.grouping, author.reading_order, author.sort_order]
   def find_author(author)
     sqlSelect = "SELECT id FROM Authors WHERE grouping=$1 AND reading=$2 AND sort=$3;"
     args = [author.grouping, author.reading_order, author.sort_order]

+

     @conn.exec_params(sqlSelect, args) do |rs|
       if rs.ntuples > 0
         return rs[0]['id']
       end
     end
     @conn.exec_params(sqlSelect, args) do |rs|
       if rs.ntuples > 0
         return rs[0]['id']
       end
     end

+

     return nil
   end
 
     return nil
   end
 


@@ -222,7 +275,7 @@ EOS
       id = next_id('author_id')
       sqlInsert = "INSERT INTO Authors(id, grouping, reading, sort) VALUES ($1, $2, $3, $4);"
       args = [id, author.grouping, author.reading_order, author.sort_order]
       id = next_id('author_id')
       sqlInsert = "INSERT INTO Authors(id, grouping, reading, sort) VALUES ($1, $2, $3, $4);"
       args = [id, author.grouping, author.reading_order, author.sort_order]

-      begin 
+      begin

         rs = @conn.exec_params(sqlInsert, args)
       rescue Exception => e
         puts sqlInsert + ":  " + args.inspect()
         rs = @conn.exec_params(sqlInsert, args)
       rescue Exception => e
         puts sqlInsert + ":  " + args.inspect()


@@ -257,7 +310,7 @@ EOS
         book.series_id = row['series']
         book.title = row['title']
         book.volume = row['volume']
         book.series_id = row['series']
         book.title = row['title']
         book.volume = row['volume']

-      end    
+      end

     rescue Exception => e
       puts sql + ": " + id
       puts e.message
     rescue Exception => e
       puts sql + ": " + id
       puts e.message


@@ -281,7 +334,7 @@ EOS
       rs = @conn.exec_params(sql, args)
     rescue Exception => e
       puts sql + ": " + args.inspect()
       rs = @conn.exec_params(sql, args)
     rescue Exception => e
       puts sql + ": " + args.inspect()

-      puts e.message 
+      puts e.message

       puts $@
     ensure
       rs.clear if rs
       puts $@
     ensure
       rs.clear if rs


@@ -378,7 +431,7 @@ EOS
     ensure
       rs.clear if rs
     end
     ensure
       rs.clear if rs
     end

-    
+

     return efs_id, mimetype
   end
 
     return efs_id, mimetype
   end
 


@@ -408,7 +461,7 @@ EOS
     id = nil
     @conn.exec("SELECT nextval('" + seq_name + "');") do |rs|
       id = rs[0]['nextval']
     id = nil
     @conn.exec("SELECT nextval('" + seq_name + "');") do |rs|
       id = rs[0]['nextval']

-    end 
+    end

     return id
   end
 
     return id
   end
 


@@ -464,7 +517,7 @@ EOS
         author_sort = row[3]
         title_grouping = row[4]
         title = row[5]
         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)
         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)


@@ -475,7 +528,7 @@ EOS
         input = row[6]
         if input.length > 0
           fast = input.split(';')
         input = row[6]
         if input.length > 0
           fast = input.split(';')

-        end 
+        end

 
         fast.each do |fast_id|
           sqlInsert = "INSERT INTO FAST_Classifications (fast, classification) VALUES ($1, $2);"
 
         fast.each do |fast_id|
           sqlInsert = "INSERT INTO FAST_Classifications (fast, classification) VALUES ($1, $2);"


@@ -501,6 +554,50 @@ EOS
     end
   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|
   def populate_series_table
     puts "Populating the Series table..."
     CSV.foreach(@basePath + '/csv/series.csv') do |row|


@@ -512,21 +609,21 @@ EOS
   end
 
   def query_books_by_author(pattern)
   end
 
   def query_books_by_author(pattern)

-    sql = 
+    sql =

 <<EOS
 <<EOS

-      SELECT b.id FROM Authors a 
-      INNER JOIN Books b ON b.author=a.id 
+      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
       LEFT OUTER JOIN Series s on s.id=b.series

-      WHERE upper(a.grouping) LIKE $1 
+      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
       ORDER BY a.grouping, b.series, b.volume, b.title
 EOS
     return exec_id_query(sql, [pattern])
   end
 
   def query_books_by_ddc

-    sql = 
+    sql =

 <<EOS
 <<EOS

-      SELECT b.id FROM Classifications c 
+      SELECT b.id FROM Classifications c

       INNER JOIN Books b ON b.classification=c.id
       ORDER BY c.ddc
 EOS
       INNER JOIN Books b ON b.classification=c.id
       ORDER BY c.ddc
 EOS


@@ -534,7 +631,7 @@ EOS
   end
 
   def query_books_by_series_id(id)
   end
 
   def query_books_by_series_id(id)

-    sql = 
+    sql =

 <<EOS
       SELECT b.id FROM Books b
       WHERE b.series = $1
 <<EOS
       SELECT b.id FROM Books b
       WHERE b.series = $1


@@ -544,9 +641,9 @@ EOS
   end
 
   def query_series_by_age(pattern)
   end
 
   def query_series_by_age(pattern)

-    sql = 
+    sql =

 <<EOS
 <<EOS

-      SELECT s.id 
+      SELECT s.id

       FROM Series s
       WHERE s.age LIKE $1
       ORDER BY s.grouping,s.descr
       FROM Series s
       WHERE s.age LIKE $1
       ORDER BY s.grouping,s.descr