store.rb

   1
   2 require 'csv'
   3 require 'fileutils'
   4 require 'inifile'
   5 require 'pg'
   6
   7 require_relative 'series'
   8 require_relative 'tconn'
   9
  10 class Store
  11   def unclassified_csv
  12     @basePath + '/csv/unclassified.csv'
  13   end
  14
  15   def initialize(config_file)
  16     @conn = nil
  17
  18     config = IniFile.load(config_file)
  19     if nil == config
  20       puts 'FATAL:  Failed to load config file "' + config_file + '".  Aborting initialization.'
  21       return
  22     end
  23
  24     section = config['database']
  25     @dbhost = section['host']
  26     @dbport = 5432
  27     @dbname = section['name']
  28     @dbuser = section['user']
  29     @dbpass = section['pass']
  30
  31     section = config['filesystem']
  32     @basePath = section['basePath']
  33   end
  34
  35   def connect
  36     @conn = TimedConn.new(PG.connect(@dbhost, @dbport, '', '', @dbname, @dbuser, @dbpass))
  37     return @conn
  38   end
  39
  40   def disconnect
  41     @conn.close()
  42   end
  43
  44   def construct_efs_path(efs_id)
  45     id_str = sprintf('%010d', efs_id)
  46     path = sprintf('%s/%s/%s/%s', id_str[0,2], id_str[2,2], id_str[4,2], id_str[6,2])
  47     name = id_str + '.dat'
  48     return path, name
  49   end
  50
  51   def cross_reference_lists
  52 puts "@@@@@@@@@@@ CROSS-REF START @@@@@@@@@@@"
  53     exec_update("TRUNCATE TABLE Lists CASCADE;", [])
  54
  55     populate_lists_table
  56 puts "@@@@@@@@@@@ CROSS-REF DONE @@@@@@@@@@@"
  57   end
  58
  59   def create_schema(skip_class)
  60     create_authors =
  61 <<EOS
  62       CREATE TABLE Authors (
  63         id          INTEGER PRIMARY KEY,
  64         grouping    VARCHAR(64),
  65         reading     VARCHAR(256),
  66         sort        VARCHAR(256)
  67       );
  68 EOS
  69
  70     create_books =
  71 <<EOS
  72       CREATE TABLE Books (
  73         id             INTEGER PRIMARY KEY,
  74         author         INTEGER REFERENCES Authors(id),
  75         classification INTEGER REFERENCES Classifications(id),
  76         cover          INTEGER,
  77         language       VARCHAR(64),
  78         description    TEXT,
  79         path           VARCHAR(256),
  80         series         INTEGER REFERENCES Series(id),
  81         title          VARCHAR(256),
  82         volume         VARCHAR(16)
  83       );
  84 EOS
  85
  86     create_classification =
  87 <<EOS
  88       CREATE TABLE Classifications (
  89         id              INTEGER PRIMARY KEY,
  90         ddc             VARCHAR(32),
  91         lcc             VARCHAR(32),
  92         author_grouping VARCHAR(64),
  93         author_sort     VARCHAR(128),
  94         title_grouping  VARCHAR(256),
  95         title           VARCHAR(256)
  96       );
  97 EOS
  98
  99     create_efs =
 100 <<EOS
 101       CREATE TABLE EFS (
 102         id          INTEGER PRIMARY KEY,
 103         mimetype    VARCHAR(64)
 104       );
 105 EOS
 106
 107     create_fast =
 108 <<EOS
 109       CREATE TABLE FAST (
 110         id          VARCHAR(32) PRIMARY KEY,
 111         descr       VARCHAR(128)
 112       );
 113 EOS
 114
 115     # Associative entity, linking FAST and Classifications tables
 116     # in a 0..n to 0..m relationship
 117     create_fast_classifications =
 118 <<EOS
 119       CREATE TABLE FAST_Classifications (
 120         fast           VARCHAR(32) REFERENCES FAST(id),
 121         classification INTEGER REFERENCES Classifications(id)
 122       );
 123 EOS
 124
 125     create_lists =
 126 <<EOS
 127       CREATE TABLE Lists (
 128         id             INTEGER PRIMARY KEY,
 129         age            VARCHAR(32),
 130         category       VARCHAR(32),
 131         code           VARCHAR(2),
 132         year           INTEGER,
 133         author         INTEGER REFERENCES Authors(id),
 134         title          VARCHAR(256)
 135       );
 136 EOS
 137
 138     # Associative entity, linking Lists and Books tables
 139     # in a 0..n to 0..m relationship
 140     create_lists_books =
 141 <<EOS
 142       CREATE TABLE Lists_Books (
 143         list           INTEGER REFERENCES Lists(id),
 144         book           INTEGER REFERENCES Books(id)
 145       );
 146 EOS
 147
 148     create_series =
 149 <<EOS
 150       CREATE TABLE Series (
 151         id          INTEGER PRIMARY KEY,
 152         age         VARCHAR(32),
 153         genre       VARCHAR(32),
 154         grouping    VARCHAR(64),
 155         code        VARCHAR(16),
 156         descr       VARCHAR(128)
 157       )
 158 EOS
 159
 160     stmts = [
 161       create_authors,
 162       create_classification,
 163       create_efs,
 164       create_fast,
 165       create_series,
 166       create_books,
 167       create_fast_classifications,
 168       create_lists,
 169       create_lists_books,
 170       'CREATE SEQUENCE author_id;',
 171       'CREATE SEQUENCE book_id;',
 172       'CREATE SEQUENCE classification_id;',
 173       'CREATE SEQUENCE efs_id;',
 174       'CREATE SEQUENCE list_id;',
 175       'CREATE SEQUENCE series_id;'
 176     ]
 177
 178     for stmt in stmts
 179       @conn.exec(stmt)
 180     end
 181
 182     if skip_class == false
 183       populate_fast_table
 184       populate_classifications_table
 185     end
 186
 187     populate_series_table
 188   end
 189
 190   def dropSchema
 191     stmts = [
 192       'DROP TABLE Lists_Books;',
 193       'DROP TABLE Lists;',
 194       'DROP TABLE Books;',
 195       'DROP TABLE FAST_Classifications;',
 196       'DROP TABLE Authors;',
 197       'DROP TABLE Classifications;',
 198       'DROP TABLE EFS;',
 199       'DROP TABLE FAST;',
 200       'DROP TABLE Series;',
 201       'DROP SEQUENCE author_id;',
 202       'DROP SEQUENCE book_id;',
 203       'DROP SEQUENCE classification_id;',
 204       'DROP SEQUENCE efs_id;',
 205       'DROP SEQUENCE list_id;',
 206       'DROP SEQUENCE series_id;'
 207     ]
 208
 209     for stmt in stmts do
 210       begin
 211         @conn.exec(stmt)
 212       rescue Exception => exc
 213         puts 'WARNING:  "' + stmt + '" failed:  ' + exc.to_s
 214       end
 215     end
 216   end
 217
 218   def find_all_authors(author_name)
 219     result = []
 220
 221     sqlSelect = "SELECT id FROM Authors WHERE grouping=$1;"
 222     args = [author_name]
 223
 224     @conn.exec_params(sqlSelect, args) do |rs|
 225       rs.each do |row|
 226         result << row['id']
 227       end
 228     end
 229
 230     result
 231   end
 232
 233   def find_author(author)
 234     sqlSelect = "SELECT id FROM Authors WHERE grouping=$1 AND reading=$2 AND sort=$3;"
 235     args = [author.grouping, author.reading_order, author.sort_order]
 236
 237     @conn.exec_params(sqlSelect, args) do |rs|
 238       if rs.ntuples > 0
 239         return rs[0]['id']
 240       end
 241     end
 242
 243     return nil
 244   end
 245
 246   def init_db(skip_class)
 247     sql = "SELECT 1 FROM pg_tables WHERE tableowner='quanlib' AND tablename='books'"
 248     found = false
 249     @conn.exec(sql).each do |row|
 250       found = true
 251     end
 252
 253     if ! found
 254       create_schema(skip_class)
 255     end
 256   end
 257
 258   def load_author(id)
 259     sqlSelect = "SELECT grouping, reading, sort FROM Authors WHERE id=$1"
 260     args = [id]
 261     @conn.exec_params(sqlSelect, args) do |rs|
 262       if rs.ntuples != 1
 263         raise "Expected 1 row for " + id + " but got " + rs.ntuples + ":  " + sqlSelect
 264       end
 265       row = rs[0]
 266       author = Author.new(row['grouping'], row['reading'], row['sort'])
 267       return author
 268     end
 269     return nil
 270   end
 271
 272   def store_author(author)
 273     id = find_author(author)
 274     if nil == id
 275       id = next_id('author_id')
 276       sqlInsert = "INSERT INTO Authors(id, grouping, reading, sort) VALUES ($1, $2, $3, $4);"
 277       args = [id, author.grouping, author.reading_order, author.sort_order]
 278       begin
 279         rs = @conn.exec_params(sqlInsert, args)
 280       rescue Exception => e
 281         puts sqlInsert + ":  " + args.inspect()
 282         puts e.message
 283         puts $@
 284       ensure
 285         rs.clear if rs
 286       end
 287     end
 288     return id
 289   end
 290
 291   def load_book(id)
 292     sql = "SELECT author, classification, cover, description, language, path, series, title, volume FROM Books WHERE id=$1;"
 293     book = nil
 294
 295     begin
 296       @conn.exec_params(sql, [id]) do |rs|
 297         if 1 != rs.ntuples
 298           raise 'Expected one row in Books for id ' + id + ', but found ' + rs.length + '.'
 299           return nil
 300         end
 301         row = rs[0]
 302
 303         book = Book.new(self)
 304         book.author = load_author(row['author'])
 305         book.classification_id = row['classification']
 306         book.cover = load_cover(row['cover'])
 307         book.description = row['description']
 308         book.language = row['language']
 309         book.path = row['path']
 310         book.series_id = row['series']
 311         book.title = row['title']
 312         book.volume = row['volume']
 313       end
 314     rescue Exception => e
 315       puts sql + ": " + id
 316       puts e.message
 317       puts $@
 318     end
 319
 320     return book
 321   end
 322
 323   def store_book(book)
 324     sql = "INSERT INTO Books (id, author, classification, cover, description, language, path, series, title, volume) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10);"
 325
 326     book_id = next_id('book_id')
 327
 328     author_id = store_author(book.author)
 329     (efs_id, mime_type) = store_cover(book)
 330
 331     args = [book_id, author_id, book.classification_id, efs_id, book.description, book.language, book.path, book.series_id, book.title, book.volume]
 332
 333     begin
 334       rs = @conn.exec_params(sql, args)
 335     rescue Exception => e
 336       puts sql + ": " + args.inspect()
 337       puts e.message
 338       puts $@
 339     ensure
 340       rs.clear if rs
 341     end
 342
 343     return book_id
 344   end
 345
 346   def find_classification(author_grouping, title_grouping)
 347     sql = "SELECT id FROM Classifications WHERE author_grouping = $1 AND title_grouping = $2;"
 348     @conn.exec_params(sql, [author_grouping, title_grouping]) do |rs|
 349       if rs.ntuples > 0
 350         return rs[0]['id']
 351       end
 352     end
 353     return nil
 354   end
 355
 356   def load_classification(id)
 357     sql  = "SELECT ddc, lcc, author_grouping, author_sort, title_grouping, title "
 358     sql += " FROM Classifications WHERE id=$1"
 359     @conn.exec_params(sql, [id]) do |rs|
 360       if rs.ntuples > 0
 361         row = rs[0]
 362         ddc = row['ddc']
 363         lcc = row['lcc']
 364         author_grouping = row['author_grouping']
 365         author = row['author_sort']
 366         title_grouping = row['title_grouping']
 367         title = row['title']
 368
 369         result = Classification.new(ddc, lcc, author_grouping, author, title_grouping, title)
 370         result.id = id
 371         return result
 372       end
 373     end
 374
 375     return nil
 376   end
 377
 378   def load_cover(id)
 379     if nil == id
 380       return nil
 381     end
 382
 383     mime_type = 'application/octet-stream'
 384
 385     sql = "SELECT mimeType FROM Efs WHERE id=$1"
 386     @conn.exec_params(sql, [id]) do |rs|
 387       if rs.ntuples != 1
 388         raise "Expected one row but got " + rs.ntuples + ": " + sql + ": " + id
 389       end
 390       mime_type = rs[0]['mimeType']
 391     end
 392
 393     (efspath, efsname) = construct_efs_path(id)
 394
 395     fullpath = @basePath + '/efs/' + efspath + '/' + efsname
 396
 397     return Cover.new(nil, fullpath, mime_type)
 398   end
 399
 400   def store_cover(book)
 401     efs_id = nil
 402     cover = book.cover()
 403
 404     if nil == cover
 405       return nil
 406     end
 407
 408     @conn.exec("SELECT nextval('efs_id')") do |rs|
 409       efs_id = rs[0]['nextval']
 410     end
 411
 412     if nil == efs_id
 413       return nil
 414     end
 415
 416     (efspath, efsname) = construct_efs_path(efs_id)
 417
 418     efspath = @basePath + '/efs/' + efspath
 419
 420     FileUtils.mkdir_p(efspath)
 421
 422     (filepath, mimetype) = cover.write_image(efspath, efsname)
 423
 424     sql = "INSERT INTO efs VALUES ($1, $2)"
 425     begin
 426       rs = @conn.exec_params(sql, [efs_id, mimetype])
 427     rescue Exception => e
 428       puts sql + ": " + efs_id + ", " + mimetype
 429       puts e.message
 430       puts $@
 431     ensure
 432       rs.clear if rs
 433     end
 434
 435     return efs_id, mimetype
 436   end
 437
 438   def exec_id_query(sql, args)
 439     ids = []
 440     @conn.exec_params(sql, args) do |rs|
 441       rs.each do |row|
 442         ids.push(row['id'])
 443       end
 444     end
 445     return ids
 446   end
 447
 448   def exec_update(sql, args)
 449     begin
 450       rs = @conn.exec_params(sql, args)
 451     rescue Exception => e
 452       puts sql + ": " + args.inspect()
 453       puts e.message
 454       puts $@
 455     ensure
 456       rs.clear if rs
 457     end
 458   end
 459
 460   def next_id(seq_name)
 461     id = nil
 462     @conn.exec("SELECT nextval('" + seq_name + "');") do |rs|
 463       id = rs[0]['nextval']
 464     end
 465     return id
 466   end
 467
 468   def get_series(grouping, code)
 469     if nil == code
 470       return nil
 471     end
 472
 473     sql = "SELECT id FROM Series WHERE grouping=$1 AND code=$2;"
 474     args = [grouping, code]
 475     @conn.exec_params(sql, args).each do |row|
 476       return row['id']
 477     end
 478
 479     # TODO:  Create a new series object here?
 480     puts 'WARNING:  series("' + grouping + '", "' + code + '") not found.'
 481     return nil
 482   end
 483
 484   def load_series(id)
 485     sql = "SELECT age,genre,grouping,code,descr FROM Series WHERE id=$1;"
 486     args = [id]
 487     @conn.exec_params(sql, args) do |rs|
 488       if rs.ntuples > 0
 489         row = rs[0]
 490         series = Series.new(id)
 491         series.age = row['age']
 492         series.genre = row['genre']
 493         series.grouping = row['grouping']
 494         series.code = row['code']
 495         series.descr = row['descr']
 496         return series
 497       end
 498     end
 499     return nil
 500   end
 501
 502   def populate_classifications_table
 503     puts "Populating the Classifications table..."
 504     first = true
 505     CSV.foreach(@basePath + '/csv/class.csv') do |row|
 506       if first
 507         # skip the header row
 508         first = false
 509       else
 510
 511         # First, add a row to the Classifications table
 512
 513         id = next_id('classification_id')
 514         ddc = row[0]
 515         lcc = row[1]
 516         author_grouping = row[2]
 517         author_sort = row[3]
 518         title_grouping = row[4]
 519         title = row[5]
 520
 521         sqlInsert = "INSERT INTO Classifications (id, ddc, lcc, author_grouping, author_sort, title_grouping, title) VALUES ($1, $2, $3, $4, $5, $6, $7);"
 522         args = [id, ddc, lcc, author_grouping, author_sort, title_grouping, title]
 523         exec_update(sqlInsert, args)
 524
 525         # Second, link up with the appropriate FAST table entries
 526
 527         fast = []
 528         input = row[6]
 529         if input.length > 0
 530           fast = input.split(';')
 531         end
 532
 533         fast.each do |fast_id|
 534           sqlInsert = "INSERT INTO FAST_Classifications (fast, classification) VALUES ($1, $2);"
 535           args = [fast_id, id]
 536           exec_update(sqlInsert, args)
 537         end
 538       end
 539     end
 540   end
 541
 542   def populate_fast_table
 543     puts "Populating the FAST table..."
 544     first = true
 545     CSV.foreach(@basePath + '/csv/fast.csv') do |row|
 546       if first
 547         first = false   # skip the header row
 548       else
 549         id = row[0]
 550         descr = row[1]
 551         sqlInsert = "INSERT INTO FAST (id, descr) VALUES ($1, $2);"
 552         exec_update(sqlInsert, [id, descr])
 553       end
 554     end
 555   end
 556
 557   def populate_lists_table
 558     puts "Populating the Lists table..."
 559
 560     CSV.foreach(@basePath + "/csv/lists.csv", headers: true) do |row|
 561       author_ids = find_all_authors(row['author'])
 562       if author_ids.empty?
 563         specification = [row['age'], row['category'], row['code'], row['year'], row['author'], row['title']]
 564           .map { |x| x.inspect }
 565           .join(', ')
 566
 567         puts "WARNING: For list entry (#{specification}), no such author was found."
 568
 569         next
 570       end
 571
 572       sqlInsert = %Q(
 573         INSERT INTO Lists (id, age, category, code, year, author, title)
 574         VALUES ($1, $2, $3, $4, $5, $6, $7);
 575       )
 576       author_ids.each do |author_id|
 577         list_id = next_id('list_id')
 578         args = [list_id, row['age'], row['category'], row['code'], row['year'], author_id, row['title']]
 579         exec_update(sqlInsert, args)
 580
 581         update_lists_books_table(list_id, author_id, row['title'])
 582       end
 583     end
 584   end
 585
 586   # Scan for books that match this Lists entry, and add any matches to the Lists_Books associative table
 587   def update_lists_books_table(list_id, author_id, title)
 588     title_pattern = Book.grouping_for_title(title).gsub('_', '%')
 589     sqlSelect = "SELECT id FROM Books WHERE author = $1 AND title LIKE $2;"
 590     args = [author_id, title_pattern]
 591
 592     @conn.exec_params(sqlSelect, args) do |rs|
 593       rs.each do |row|
 594         sqlInsert = "INSERT INTO Lists_Books (list, book) VALUES ($1, $2)"
 595         args = [list_id, row['id']]
 596         exec_update(sqlInsert, args)
 597       end
 598     end
 599   end
 600
 601   def populate_series_table
 602     puts "Populating the Series table..."
 603     CSV.foreach(@basePath + '/csv/series.csv') do |row|
 604       id = next_id('series_id')
 605       sqlInsert = "INSERT INTO Series (id, age, genre, grouping, code, descr) VALUES ($1, $2, $3, $4, $5, $6);"
 606       args = [id] + row
 607       exec_update(sqlInsert, args)
 608     end
 609   end
 610
 611   def query_books_by_author(pattern)
 612     sql =
 613 <<EOS
 614       SELECT b.id FROM Authors a
 615       INNER JOIN Books b ON b.author=a.id
 616       LEFT OUTER JOIN Series s on s.id=b.series
 617       WHERE upper(a.grouping) LIKE $1
 618       ORDER BY a.grouping, b.series, b.volume, b.title
 619 EOS
 620     return exec_id_query(sql, [pattern])
 621   end
 622
 623   def query_books_by_ddc
 624     sql =
 625 <<EOS
 626       SELECT b.id FROM Classifications c
 627       INNER JOIN Books b ON b.classification=c.id
 628       ORDER BY c.ddc
 629 EOS
 630     return exec_id_query(sql, [])
 631   end
 632
 633   def query_books_by_series_id(id)
 634     sql =
 635 <<EOS
 636       SELECT b.id FROM Books b
 637       WHERE b.series = $1
 638       ORDER BY b.volume,b.title
 639 EOS
 640     return exec_id_query(sql, [id])
 641   end
 642
 643   def query_series_by_age(pattern)
 644     sql =
 645 <<EOS
 646       SELECT s.id
 647       FROM Series s
 648       WHERE s.age LIKE $1
 649       ORDER BY s.grouping,s.descr
 650 EOS
 651     return exec_id_query(sql, [pattern])
 652   end
 653 end
 654