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