X-Git-Url: http://jaekl.net/gitweb/?p=quanlib.git;a=blobdiff_plain;f=store.rb;h=946c43c4f9056a53a1e753b2240babeb90c08e81;hp=645224a7c4bfa22bdcc66d08e031ad91d7c0f176;hb=b31122c5ed23e2e77b527c0979b6355e9e3dda1f;hpb=4b53af822cda819dd82d0d3e7ed066c2966ae4bf diff --git a/store.rb b/store.rb index 645224a..946c43c 100644 --- a/store.rb +++ b/store.rb @@ -1,10 +1,11 @@ +require 'csv' require 'fileutils' require 'pg' class Store def initialize - @basepath = '/home/chris/prog/quanlib/efs' # TODO: FIXME: configure this in a sane way + @basepath = '/arc/quanlib' # TODO: FIXME: configure this in a sane way @conn = nil #@dburl = 'dbi:Pg:quanlib:localhost' @@ -36,7 +37,7 @@ class Store create_authors = < 0 + return rs[0]['id'] + end + end + return nil + end + def init_db sql = "SELECT 1 FROM pg_tables WHERE tableowner='quanlib' AND tablename='books'" found = false @@ -102,18 +136,8 @@ EOS end end - def find_author(author) - sqlSelect = "SELECT id FROM Authors WHERE grouping=$1 AND reading=$2 AND sort=$3;" - args = [author.grouping, author.reading_order, author.sort_order] - @conn.exec_params(sqlSelect, args) do |rs| - if rs.ntuples > 0 - return rs[0]['id'] - end - end - return nil - end - def load_author(id) + #puts 'DEBUG: load_author(' + id + ')' sqlSelect = "SELECT grouping, reading, sort FROM Authors WHERE id=$1" args = [id] @conn.exec_params(sqlSelect, args) do |rs| @@ -121,15 +145,20 @@ EOS raise "Expected 1 row for " + id + " but got " + rs.ntuples + ": " + sqlSelect end row = rs[0] - return Author.new(row['grouping'], row['reading'], row['sort']) + author = Author.new(row['grouping'], row['reading'], row['sort']) + #puts 'DEBUG: author: ' + author.inspect() + return author end + #puts 'DEBUG: NOT FOUND' + return nil end def store_author(author) id = find_author(author) if nil == id - sqlInsert = "INSERT INTO Authors(grouping, reading, sort) VALUES ($1, $2, $3);" - args = [author.grouping, author.reading_order, author.sort_order] + id = next_id('author_id') + sqlInsert = "INSERT INTO Authors(id, grouping, reading, sort) VALUES ($1, $2, $3, $4);" + args = [id, author.grouping, author.reading_order, author.sort_order] begin rs = @conn.exec_params(sqlInsert, args) rescue Exception => e @@ -140,10 +169,11 @@ EOS rs.clear if rs end end - return find_author(author) + return id end def load_book(id) + #puts 'DEBUG: load_book(' + id + ')' sql = "SELECT author, cover, description, path, series, title, volume FROM Books WHERE id=$1;" book = nil @@ -155,30 +185,34 @@ EOS end row = rs[0] - book = Book.new() + book = Book.new(self) book.author = load_author(row['author']) book.cover = load_cover(row['cover']) book.description = row['description'] book.path = row['path'] - book.series = row['series'] + book.series_id = row['series'] book.title = row['title'] book.volume = row['volume'] end rescue Exception => e puts sql + ": " + id puts e.message + puts $@ end + #puts 'DEBUG: loaded book: ' + book.inspect() return book end def store_book(book) - sql = "INSERT INTO Books (author, cover, description, path, series, title, volume) VALUES ($1, $2, $3, $4, $5, $6, $7);" + sql = "INSERT INTO Books (id, author, cover, description, path, series, title, volume) VALUES ($1, $2, $3, $4, $5, $6, $7, $8);" + + book_id = next_id('book_id') author_id = store_author(book.author) (efs_id, mime_type) = store_cover(book) - args = [author_id, efs_id, book.description(), book.path(), book.series(), book.title(), book.volume()] + args = [book_id, author_id, efs_id, book.description(), book.path(), book.series_id(), book.title(), book.volume()] begin rs = @conn.exec_params(sql, args) @@ -189,14 +223,32 @@ EOS ensure rs.clear if rs end + + return book_id end def load_cover(id) + if nil == id + return nil + end + + mime_type = 'application/octet-stream' + + sql = "SELECT mimeType FROM Efs WHERE id=$1" + @conn.exec_params(sql, [id]) do |rs| + if rs.ntuples != 1 + raise "Expected one row but got " + rs.ntuples + ": " + sql + ": " + id + end + mime_type = rs[0]['mimeType'] + end + (efspath, efsname) = construct_efs_path(id) - efspath = @basepath + '/' + efspath - cover = Cover.new() - cover.load_image(efspath + '/' + efsname) - return cover + + File.open(@basepath + '/efs/' + efspath + '/' + efsname, 'rb') do |is| + return Cover.new(is, efsname, mime_type) + end + + return nil end def store_cover(book) @@ -217,7 +269,7 @@ EOS (efspath, efsname) = construct_efs_path(efs_id) - efspath = @basepath + '/' + efspath + efspath = @basepath + '/efs/' + efspath FileUtils.mkdir_p(efspath) @@ -227,6 +279,7 @@ EOS begin rs = @conn.exec_params(sql, [efs_id, mimetype]) rescue Exception => e + puts sql + ": " + efs_id + ", " + mimetype puts e.message puts $@ ensure @@ -235,5 +288,77 @@ EOS return efs_id, mimetype end + + def next_id(seq_name) + id = nil + @conn.exec("SELECT nextval('" + seq_name + "');") do |rs| + id = rs[0]['nextval'] + end + return id + end + + def get_series(grouping, code) + if nil == code + return nil + end + + sql = "SELECT id FROM Series WHERE grouping=$1 AND code=$2;" + args = [grouping, code] + @conn.exec_params(sql, args).each do |row| + return row['id'] + end + + # TODO: Create a new series object here? + puts 'WARNING: series("' + grouping + '", "' + code + '") not found.' + return nil + end + + def load_series(id) + sql = "SELECT descr FROM Series WHERE id=$1;" + args = [id] + @conn.exec_params(sql, args) do |rs| + if rs.ntuples > 0 + return rs[0]['descr'] + end + end + return nil + end + + def populate_series_table + puts "Populating the Series table..." + CSV.foreach(@basepath + '/csv/series.csv') do |row| + id = next_id('series_id') + sqlInsert = "INSERT INTO Series (id, age, genre, grouping, code, descr) VALUES ($1, $2, $3, $4, $5, $6);" + args = [id] + row + begin + # DEBUG: puts 'SQL> ' + sqlInsert + ': ' + args.inspect() + rs = @conn.exec_params(sqlInsert, args) + rescue Exception => e + puts sqlInsert + ": " + args.inspect() + puts e.message + puts $@ + ensure + rs.clear if rs + end + end + end + + def query_books_by_author(pattern) + sql = +<