+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'
create_authors =
<<EOS
CREATE TABLE Authors (
- id SERIAL PRIMARY KEY,
+ id INTEGER PRIMARY KEY,
grouping VARCHAR(64),
reading VARCHAR(128),
sort VARCHAR(128)
create_books =
<<EOS
CREATE TABLE Books (
- id SERIAL PRIMARY KEY,
+ id INTEGER PRIMARY KEY,
author INTEGER REFERENCES Authors(id),
cover INTEGER,
description TEXT,
path VARCHAR(256),
- series VARCHAR(128),
+ series INTEGER REFERENCES Series(id),
title VARCHAR(196),
volume VARCHAR(16)
);
);
EOS
+ create_series =
+<<EOS
+ CREATE TABLE Series (
+ id INTEGER PRIMARY KEY,
+ age VARCHAR(32),
+ genre VARCHAR(32),
+ grouping VARCHAR(64),
+ code VARCHAR(16),
+ descr VARCHAR(128)
+ )
+EOS
+
stmts = [
create_authors,
- create_books,
create_efs,
- 'CREATE SEQUENCE efs_id;'
+ create_series,
+ create_books,
+ 'CREATE SEQUENCE author_id;',
+ 'CREATE SEQUENCE book_id;',
+ 'CREATE SEQUENCE efs_id;',
+ 'CREATE SEQUENCE series_id;'
]
for stmt in stmts
@conn.exec(stmt)
end
+
+ populate_series_table()
end
def dropSchema
'DROP TABLE Books;',
'DROP TABLE Authors;',
'DROP TABLE EFS;',
- 'DROP SEQUENCE efs_id;'
+ 'DROP TABLE Series;',
+ 'DROP SEQUENCE author_id;',
+ 'DROP SEQUENCE book_id;',
+ 'DROP SEQUENCE efs_id;',
+ 'DROP SEQUENCE series_id;'
]
for stmt in stmts do
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 init_db
sql = "SELECT 1 FROM pg_tables WHERE tableowner='quanlib' AND tablename='books'"
found = false
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|
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
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
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)
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)
(efspath, efsname) = construct_efs_path(efs_id)
- efspath = @basepath + '/' + efspath
+ efspath = @basepath + '/efs/' + efspath
FileUtils.mkdir_p(efspath)
begin
rs = @conn.exec_params(sql, [efs_id, mimetype])
rescue Exception => e
+ puts sql + ": " + efs_id + ", " + mimetype
puts e.message
puts $@
ensure
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 =
+<<EOS
+ SELECT b.id FROM Authors a
+ INNER JOIN Books b ON b.author=a.id
+ LEFT OUTER JOIN Series s on s.id=b.series
+ WHERE upper(a.grouping) LIKE $1
+ ORDER BY a.grouping, b.series, b.volume, b.title
+EOS
+ book_ids = []
+ @conn.exec_params(sql, [pattern]) do |rs|
+ rs.each do |row|
+ book_ids.push(row['id'])
+ end
+ end
+ return book_ids
+ end
end
projects / quanlib.git / blobdiff