store.rb

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