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(128),
  45         sort        VARCHAR(128)
  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_cover(id)
 293     if nil == id
 294       return nil
 295     end
 296
 297     mime_type = 'application/octet-stream'
 298
 299     sql = "SELECT mimeType FROM Efs WHERE id=$1"
 300     @conn.exec_params(sql, [id]) do |rs|
 301       if rs.ntuples != 1
 302         raise "Expected one row but got " + rs.ntuples + ": " + sql + ": " + id
 303       end
 304       mime_type = rs[0]['mimeType']
 305     end
 306
 307     (efspath, efsname) = construct_efs_path(id)
 308
 309     fullpath = @basepath + '/efs/' + efspath + '/' + efsname
 310
 311     return Cover.new(nil, fullpath, mime_type)
 312
 313     #File.open(fullpath, 'rb') do |is|
 314     #  return Cover.new(is, fullpath, mime_type)
 315     #end
 316     #
 317     #return nil
 318   end
 319
 320   def store_cover(book)
 321     efs_id = nil
 322     cover = book.cover()
 323
 324     if nil == cover
 325       return nil
 326     end
 327
 328     @conn.exec("SELECT nextval('efs_id')") do |rs|
 329       efs_id = rs[0]['nextval']
 330     end
 331
 332     if nil == efs_id
 333       return nil
 334     end
 335
 336     (efspath, efsname) = construct_efs_path(efs_id)
 337
 338     efspath = @basepath + '/efs/' + efspath
 339
 340     FileUtils.mkdir_p(efspath)
 341
 342     (filepath, mimetype) = cover.write_image(efspath, efsname)
 343
 344     sql = "INSERT INTO efs VALUES ($1, $2)"
 345     begin
 346       rs = @conn.exec_params(sql, [efs_id, mimetype])
 347     rescue Exception => e
 348       puts sql + ": " + efs_id + ", " + mimetype
 349       puts e.message
 350       puts $@
 351     ensure
 352       rs.clear if rs
 353     end
 354
 355     return efs_id, mimetype
 356   end
 357
 358   def exec_id_query(sql, args)
 359     ids = []
 360     @conn.exec_params(sql, args) do |rs|
 361       rs.each do |row|
 362         ids.push(row['id'])
 363       end
 364     end
 365     return ids
 366   end
 367
 368   def exec_update(sql, args)
 369     begin
 370       rs = @conn.exec_params(sql, args)
 371     rescue Exception => e
 372       puts sql + ": " + args.inspect()
 373       puts e.message
 374       puts $@
 375     ensure
 376       rs.clear if rs
 377     end
 378   end
 379
 380   def next_id(seq_name)
 381     id = nil
 382     @conn.exec("SELECT nextval('" + seq_name + "');") do |rs|
 383       id = rs[0]['nextval']
 384     end
 385     return id
 386   end
 387
 388   def get_series(grouping, code)
 389     if nil == code
 390       return nil
 391     end
 392
 393     sql = "SELECT id FROM Series WHERE grouping=$1 AND code=$2;"
 394     args = [grouping, code]
 395     @conn.exec_params(sql, args).each do |row|
 396       return row['id']
 397     end
 398
 399     # TODO:  Create a new series object here?
 400     puts 'WARNING:  series("' + grouping + '", "' + code + '") not found.'
 401     return nil
 402   end
 403
 404   def load_series(id)
 405     sql = "SELECT age,genre,grouping,code,descr FROM Series WHERE id=$1;"
 406     args = [id]
 407     @conn.exec_params(sql, args) do |rs|
 408       if rs.ntuples > 0
 409         row = rs[0]
 410         series = Series.new(id)
 411         series.age = row['age']
 412         series.genre = row['genre']
 413         series.grouping = row['grouping']
 414         series.code = row['code']
 415         series.descr = row['descr']
 416         return series
 417       end
 418     end
 419     return nil
 420   end
 421
 422   def populate_classifications_table
 423     puts "Populating the Classifications table..."
 424     first = true
 425     CSV.foreach(@basepath + '/csv/class.csv') do |row|
 426       if first
 427         # skip the header row
 428         first = false
 429       else
 430
 431         # First, add a row to the Classifications table
 432
 433         id = next_id('classification_id')
 434         ddc = row[0]
 435         lcc = row[1]
 436         author_grouping = row[2]
 437         author_sort = row[3]
 438         title_grouping = row[4]
 439         title = row[5]
 440
 441         sqlInsert = "INSERT INTO Classifications (id, ddc, lcc, author_grouping, author_sort, title_grouping, title) VALUES ($1, $2, $3, $4, $5, $6, $7);"
 442         args = [id, ddc, lcc, author_grouping, author_sort, title_grouping, title]
 443         exec_update(sqlInsert, args)
 444
 445         # Second, link up with the appropriate FAST table entries
 446
 447         fast = []
 448         input = row[6]
 449         if input.length > 0
 450           fast = input.split(';')
 451         end
 452
 453         fast.each do |fast_id|
 454           sqlInsert = "INSERT INTO FAST_Classifications (fast, classification) VALUES ($1, $2);"
 455           args = [fast_id, id]
 456           exec_update(sqlInsert, args)
 457         end
 458       end
 459     end
 460   end
 461
 462   def populate_fast_table
 463     puts "Populating the FAST table..."
 464     first = true
 465     CSV.foreach(@basepath + '/csv/fast.csv') do |row|
 466       if first
 467         first = false   # skip the header row
 468       else
 469         id = row[0]
 470         descr = row[1]
 471         sqlInsert = "INSERT INTO FAST (id, descr) VALUES ($1, $2);"
 472         exec_update(sqlInsert, [id, descr])
 473       end
 474     end
 475   end
 476
 477   def populate_series_table
 478     puts "Populating the Series table..."
 479     CSV.foreach(@basepath + '/csv/series.csv') do |row|
 480       id = next_id('series_id')
 481       sqlInsert = "INSERT INTO Series (id, age, genre, grouping, code, descr) VALUES ($1, $2, $3, $4, $5, $6);"
 482       args = [id] + row
 483       exec_update(sqlInsert, args)
 484     end
 485   end
 486
 487   def query_books_by_author(pattern)
 488     sql =
 489 <<EOS
 490       SELECT b.id FROM Authors a
 491       INNER JOIN Books b ON b.author=a.id
 492       LEFT OUTER JOIN Series s on s.id=b.series
 493       WHERE upper(a.grouping) LIKE $1
 494       ORDER BY a.grouping, b.series, b.volume, b.title
 495 EOS
 496     return exec_id_query(sql, [pattern])
 497   end
 498
 499   def query_books_by_ddc
 500     sql =
 501 <<EOS
 502       SELECT b.id FROM Classifications c
 503       INNER JOIN Books b ON b.classification=c.id
 504       ORDER BY c.ddc
 505 EOS
 506     return exec_id_query(sql, [])
 507   end
 508
 509   def query_books_by_series_id(id)
 510     sql =
 511 <<EOS
 512       SELECT b.id FROM Books b
 513       WHERE b.series = $1
 514       ORDER BY b.volume,b.title
 515 EOS
 516     return exec_id_query(sql, [id])
 517   end
 518
 519   def query_series_by_age(pattern)
 520     sql =
 521 <<EOS
 522       SELECT s.id
 523       FROM Series s
 524       WHERE s.age LIKE $1
 525       ORDER BY s.grouping,s.descr
 526 EOS
 527     return exec_id_query(sql, [pattern])
 528   end
 529 end
 530