X-Git-Url: http://jaekl.net/gitweb/?p=quanlib.git;a=blobdiff_plain;f=store.rb;fp=store.rb;h=660fc80bb64464fcb9aa20e18aa4c9a797666294;hp=05f44132d1609af54f4cbb3e47befc69161fbce3;hb=2c6d69af97c152524366d3fefe1808dfb78f8f56;hpb=fcaeedd4d1c128ff84371c0a7db5d0af6751492a

diff --git a/store.rb b/store.rb
index 05f4413..660fc80 100644
--- a/store.rb
+++ b/store.rb
@@ -49,14 +49,28 @@ EOS
     create_books = 
 <<EOS
       CREATE TABLE Books (
-        id          INTEGER PRIMARY KEY,
-        author      INTEGER REFERENCES Authors(id),
-        cover       INTEGER,
-        description TEXT,
-        path        VARCHAR(256),
-        series      INTEGER REFERENCES Series(id),
-        title       VARCHAR(196),
-        volume      VARCHAR(16)
+        id             INTEGER PRIMARY KEY,
+        author         INTEGER REFERENCES Authors(id),
+        classification INTEGER REFERENCES Classifications(id),
+        cover          INTEGER,
+        description    TEXT,
+        path           VARCHAR(256),
+        series         INTEGER REFERENCES Series(id),
+        title          VARCHAR(256),
+        volume         VARCHAR(16)
+      );
+EOS
+
+    create_classification =
+<<EOS
+      CREATE TABLE Classifications (
+        id              INTEGER PRIMARY KEY,
+        ddc             VARCHAR(32),
+        lcc             VARCHAR(32),
+        author_grouping VARCHAR(64),
+        author_sort     VARCHAR(128),
+        title_grouping  VARCHAR(256),
+        title           VARCHAR(256)
       );
 EOS
 
@@ -68,6 +82,24 @@ EOS
       );
 EOS
 
+    create_fast = 
+<<EOS
+      CREATE TABLE FAST (
+        id          VARCHAR(32) PRIMARY KEY,
+        descr       VARCHAR(128)
+      );
+EOS
+
+    # Associative entity, linking FAST and Classifications tables
+    # in a 0..n to 0..m relationship
+    create_fast_classifications =
+<<EOS
+      CREATE TABLE FAST_Classifications (
+        fast           VARCHAR(32) REFERENCES FAST(id),
+        classification INTEGER REFERENCES Classifications(id)
+      );
+EOS
+
     create_series = 
 <<EOS
       CREATE TABLE Series (
@@ -82,11 +114,15 @@ EOS
 
     stmts = [
       create_authors,
+      create_classification,
       create_efs,
+      create_fast,
       create_series,
       create_books,
+      create_fast_classifications,
       'CREATE SEQUENCE author_id;',
       'CREATE SEQUENCE book_id;',
+      'CREATE SEQUENCE classification_id;',
       'CREATE SEQUENCE efs_id;',
       'CREATE SEQUENCE series_id;'
     ]
@@ -95,23 +131,33 @@ EOS
       @conn.exec(stmt)
     end
 
+    populate_fast_table()
+    populate_classifications_table()
     populate_series_table()
   end
 
   def dropSchema
     stmts = [
       'DROP TABLE Books;',
+      'DROP TABLE FAST_Classifications;',
       'DROP TABLE Authors;',
+      'DROP TABLE Classifications;',
       'DROP TABLE EFS;',
+      'DROP TABLE FAST;',
       'DROP TABLE Series;',
       'DROP SEQUENCE author_id;',
       'DROP SEQUENCE book_id;',
+      'DROP SEQUENCE classification_id;',
       'DROP SEQUENCE efs_id;',
       'DROP SEQUENCE series_id;'
     ]
 
     for stmt in stmts do
-      @conn.exec(stmt)
+      begin
+        @conn.exec(stmt)
+      rescue Exception => exc
+        puts 'WARNING:  "' + stmt + '" failed:  ' + exc.to_s
+      end
     end
   end
 
@@ -176,7 +222,7 @@ EOS
 
   def load_book(id)
     #puts 'DEBUG:  load_book(' + id + ')'
-    sql = "SELECT author, cover, description, path, series, title, volume FROM Books WHERE id=$1;"
+    sql = "SELECT author, classification, cover, description, path, series, title, volume FROM Books WHERE id=$1;"
     book = nil
 
     begin
@@ -189,6 +235,7 @@ EOS
 
         book = Book.new(self)
         book.author = load_author(row['author'])
+        book.classification_id = row['classification']
         book.cover = load_cover(row['cover'])
         book.description = row['description']
         book.path = row['path']
@@ -207,14 +254,14 @@ EOS
   end
 
   def store_book(book)
-    sql = "INSERT INTO Books (id, author, cover, description, path, series, title, volume) VALUES ($1, $2, $3, $4, $5, $6, $7, $8);"
+    sql = "INSERT INTO Books (id, author, classification, cover, description, path, series, title, volume) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9);"
 
     book_id = next_id('book_id')
 
     author_id = store_author(book.author)
     (efs_id, mime_type) = store_cover(book)
 
-    args = [book_id, author_id, efs_id, book.description(), book.path(), book.series_id(), book.title(), book.volume()]
+    args = [book_id, author_id, book.classification_id, efs_id, book.description(), book.path(), book.series_id(), book.title(), book.volume()]
 
     begin
       rs = @conn.exec_params(sql, args)
@@ -229,6 +276,19 @@ EOS
     return book_id
   end
 
+  def find_classification(author_grouping, title_grouping)
+    #puts 'find_classification("' + author_grouping.inspect + '", "' + title_grouping.inspect + '")...'
+    sql = "SELECT id FROM Classifications WHERE author_grouping = $1 AND title_grouping = $2;"
+    @conn.exec_params(sql, [author_grouping, title_grouping]) do |rs|
+      if rs.ntuples > 0
+        #puts '  --> ' + rs[0]['id'].inspect
+        return rs[0]['id']
+      end
+    end
+    #puts '  --> NIL'
+    return nil
+  end
+
   def load_cover(id)
     if nil == id
       return nil
@@ -295,6 +355,28 @@ EOS
     return efs_id, mimetype
   end
 
+  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|
@@ -337,22 +419,68 @@ EOS
     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_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
+      exec_update(sqlInsert, args)
     end
   end
 
@@ -365,13 +493,17 @@ EOS
       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
+    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)
@@ -381,13 +513,7 @@ EOS
       WHERE b.series = $1
       ORDER BY b.volume,b.title
 EOS
-    book_ids = []
-    @conn.exec_params(sql, [id]) do |rs|
-      rs.each do |row|
-        book_ids.push(row['id'])
-      end
-    end
-    return book_ids
+    return exec_id_query(sql, [id])
   end
 
   def query_series_by_age(pattern)
@@ -398,13 +524,7 @@ EOS
       WHERE s.age LIKE $1
       ORDER BY s.grouping,s.descr
 EOS
-    series_ids = []
-    @conn.exec_params(sql, [pattern]) do |rs|
-      rs.each do |row|
-        series_ids.push(row['id'])
-      end
-    end
-    return series_ids
+    return exec_id_query(sql, [pattern])
   end
 end