store.rb

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