+require 'csv'
require 'fileutils'
+require 'inifile'
require 'pg'
+require_relative 'series'
+require_relative 'tconn'
+
class Store
- def initialize
- @basepath = '/home/chris/prog/quanlib/efs' # TODO: FIXME: configure this in a sane way
+ def unclassified_csv
+ @basePath + '/csv/unclassified.csv'
+ end
+
+ def initialize(config_file)
@conn = nil
- #@dburl = 'dbi:Pg:quanlib:localhost'
- @dbhost = "localhost"
+ config = IniFile.load(config_file)
+ if nil == config
+ puts 'FATAL: Failed to load config file "' + config_file + '". Aborting initialization.'
+ return
+ end
+
+ section = config['database']
+ @dbhost = section['host']
@dbport = 5432
- @dbname = 'quanlib'
- @dbuser = 'quanlib'
- @dbpass = 'quanlib'
+ @dbname = section['name']
+ @dbuser = section['user']
+ @dbpass = section['pass']
+
+ section = config['filesystem']
+ @basePath = section['basePath']
end
def connect
- # @conn = PGconn.connect('localhost', 5432, '', '', 'quanlib', 'quanlib', 'quanlib')
- @conn = PG.connect('localhost', 5432, '', '', 'quanlib', 'quanlib', 'quanlib')
+ @conn = TimedConn.new(PG.connect(@dbhost, @dbport, '', '', @dbname, @dbuser, @dbpass))
return @conn
end
return path, name
end
- def create_schema
- create_authors =
+ def cross_reference_lists
+puts "@@@@@@@@@@@ CROSS-REF START @@@@@@@@@@@"
+ exec_update("TRUNCATE TABLE Lists CASCADE;", [])
+
+ populate_lists_table
+puts "@@@@@@@@@@@ CROSS-REF DONE @@@@@@@@@@@"
+ end
+
+ def create_schema(skip_class)
+ create_authors =
<<EOS
CREATE TABLE Authors (
- id SERIAL PRIMARY KEY,
+ id INTEGER PRIMARY KEY,
grouping VARCHAR(64),
- reading VARCHAR(128),
- sort VARCHAR(128)
+ reading VARCHAR(256),
+ sort VARCHAR(256)
);
EOS
- create_books =
+ create_books =
<<EOS
CREATE TABLE Books (
- id SERIAL PRIMARY KEY,
- author INTEGER REFERENCES Authors(id),
- cover INTEGER,
- description TEXT,
- path VARCHAR(256),
- series VARCHAR(128),
- title VARCHAR(196),
- volume VARCHAR(16)
+ id INTEGER PRIMARY KEY,
+ arrived TIMESTAMP,
+ author INTEGER REFERENCES Authors(id),
+ classification INTEGER REFERENCES Classifications(id),
+ cover INTEGER,
+ language VARCHAR(64),
+ description TEXT,
+ path VARCHAR(256),
+ series INTEGER REFERENCES Series(id),
+ title VARCHAR(256),
+ volume VARCHAR(16)
+ );
+EOS
+
+ create_classification =
+<<EOS
+ CREATE TABLE Classifications (
+ id INTEGER PRIMARY KEY,
+ ddc VARCHAR(32),
+ lcc VARCHAR(32),
+ author_grouping VARCHAR(64),
+ author_sort VARCHAR(128),
+ title_grouping VARCHAR(256),
+ title VARCHAR(256)
);
EOS
- create_efs =
+ create_efs =
<<EOS
CREATE TABLE EFS (
- id INTEGER,
+ id INTEGER PRIMARY KEY,
mimetype VARCHAR(64)
);
EOS
+ create_fast =
+<<EOS
+ CREATE TABLE FAST (
+ id VARCHAR(32) PRIMARY KEY,
+ descr VARCHAR(128)
+ );
+EOS
+
+ # Associative entity, linking FAST and Classifications tables
+ # in a 0..n to 0..m relationship
+ create_fast_classifications =
+<<EOS
+ CREATE TABLE FAST_Classifications (
+ fast VARCHAR(32) REFERENCES FAST(id),
+ classification INTEGER REFERENCES Classifications(id)
+ );
+EOS
+
+ create_lists =
+<<EOS
+ CREATE TABLE Lists (
+ id INTEGER PRIMARY KEY,
+ age VARCHAR(32),
+ category VARCHAR(32),
+ code VARCHAR(2),
+ year INTEGER,
+ author INTEGER REFERENCES Authors(id),
+ title VARCHAR(256)
+ );
+EOS
+
+ # Associative entity, linking Lists and Books tables
+ # in a 0..n to 0..m relationship
+ create_lists_books =
+<<EOS
+ CREATE TABLE Lists_Books (
+ list INTEGER REFERENCES Lists(id),
+ book INTEGER REFERENCES Books(id)
+ );
+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_classification,
create_efs,
- 'CREATE SEQUENCE efs_id;'
+ create_fast,
+ create_series,
+ create_books,
+ create_fast_classifications,
+ create_lists,
+ create_lists_books,
+ 'CREATE SEQUENCE author_id;',
+ 'CREATE SEQUENCE book_id;',
+ 'CREATE SEQUENCE classification_id;',
+ 'CREATE SEQUENCE efs_id;',
+ 'CREATE SEQUENCE list_id;',
+ 'CREATE SEQUENCE series_id;'
]
for stmt in stmts
@conn.exec(stmt)
end
+
+ if skip_class == false
+ populate_fast_table
+ populate_classifications_table
+ end
+
+ populate_series_table
end
def dropSchema
stmts = [
+ 'DROP TABLE Lists_Books;',
+ 'DROP TABLE Lists;',
'DROP TABLE Books;',
+ 'DROP TABLE FAST_Classifications;',
'DROP TABLE Authors;',
+ 'DROP TABLE Classifications;',
'DROP TABLE EFS;',
- 'DROP SEQUENCE efs_id;'
+ 'DROP TABLE FAST;',
+ 'DROP TABLE Series;',
+ 'DROP SEQUENCE author_id;',
+ 'DROP SEQUENCE book_id;',
+ 'DROP SEQUENCE classification_id;',
+ 'DROP SEQUENCE efs_id;',
+ 'DROP SEQUENCE list_id;',
+ 'DROP SEQUENCE series_id;'
]
for stmt in stmts do
- @conn.exec(stmt)
+ begin
+ @conn.exec(stmt)
+ rescue Exception => exc
+ puts 'WARNING: "' + stmt + '" failed: ' + exc.to_s
+ end
+ end
+ end
+
+ def find_all_authors(author_name)
+ result = []
+
+ sqlSelect = "SELECT id FROM Authors WHERE grouping=$1;"
+ args = [author_name]
+
+ @conn.exec_params(sqlSelect, args) do |rs|
+ rs.each do |row|
+ result << row['id']
+ end
end
+
+ result
end
- def init_db
+ 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(skip_class)
sql = "SELECT 1 FROM pg_tables WHERE tableowner='quanlib' AND tablename='books'"
found = false
@conn.exec(sql).each do |row|
end
if ! found
- create_schema()
+ create_schema(skip_class)
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]
+ def load_author(id)
+ sqlSelect = "SELECT grouping, reading, sort FROM Authors WHERE id=$1"
+ args = [id]
@conn.exec_params(sqlSelect, args) do |rs|
- if rs.ntuples > 0
- return rs[0]['id']
+ if rs.ntuples != 1
+ raise "Expected 1 row for " + id + " but got " + rs.ntuples + ": " + sqlSelect
end
+ row = rs[0]
+ author = Author.new(row['grouping'], row['reading'], row['sort'])
+ return author
end
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]
- begin
+ 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
puts sqlInsert + ": " + args.inspect()
rs.clear if rs
end
end
- return find_author(author)
+ return id
+ end
+
+ def load_book(id)
+ sql = "SELECT author, classification, cover, description, language, path, series, title, volume FROM Books WHERE id=$1;"
+ book = nil
+
+ begin
+ @conn.exec_params(sql, [id]) do |rs|
+ if 1 != rs.ntuples
+ raise 'Expected one row in Books for id ' + id + ', but found ' + rs.length + '.'
+ return nil
+ end
+ row = rs[0]
+
+ book = Book.new(self)
+ book.author = load_author(row['author'])
+ book.classification_id = row['classification']
+ book.cover = load_cover(row['cover'])
+ book.description = row['description']
+ book.language = row['language']
+ book.path = row['path']
+ 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
+
+ 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, arrived, author, classification, cover, description, language, path, series, title, volume) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11);"
+
+ 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, book.arrived, author_id, book.classification_id, efs_id, book.description, book.language, book.path, book.series_id, book.title, book.volume]
begin
rs = @conn.exec_params(sql, args)
rescue Exception => e
puts sql + ": " + args.inspect()
- puts e.message
+ puts e.message
puts $@
ensure
rs.clear if rs
end
+
+ return book_id
+ end
+
+ def find_classification(author_grouping, title_grouping)
+ sql = "SELECT id FROM Classifications WHERE author_grouping = $1 AND title_grouping = $2;"
+ @conn.exec_params(sql, [author_grouping, title_grouping]) do |rs|
+ if rs.ntuples > 0
+ return rs[0]['id']
+ end
+ end
+ return nil
+ end
+
+ def load_classification(id)
+ sql = "SELECT ddc, lcc, author_grouping, author_sort, title_grouping, title "
+ sql += " FROM Classifications WHERE id=$1"
+ @conn.exec_params(sql, [id]) do |rs|
+ if rs.ntuples > 0
+ row = rs[0]
+ ddc = row['ddc']
+ lcc = row['lcc']
+ author_grouping = row['author_grouping']
+ author = row['author_sort']
+ title_grouping = row['title_grouping']
+ title = row['title']
+
+ result = Classification.new(ddc, lcc, author_grouping, author, title_grouping, title)
+ result.id = id
+ return result
+ end
+ end
+
+ return nil
+ 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)
+
+ fullpath = @basePath + '/efs/' + efspath + '/' + efsname
+
+ return Cover.new(nil, fullpath, mime_type)
end
def store_cover(book)
(efspath, efsname) = construct_efs_path(efs_id)
- efspath = @basepath + '/' + efspath
+ efspath = @basePath + '/efs/' + efspath
FileUtils.mkdir_p(efspath)
- (filepath, mimetype) = cover.writeImage(efspath, efsname)
+ (filepath, mimetype) = cover.write_image(efspath, efsname)
sql = "INSERT INTO efs VALUES ($1, $2)"
begin
rs = @conn.exec_params(sql, [efs_id, mimetype])
rescue Exception => e
+ puts sql + ": " + efs_id + ", " + mimetype
puts e.message
puts $@
ensure
rs.clear if rs
end
-
+
return efs_id, mimetype
end
+
+ def exec_id_query(sql, args)
+ ids = []
+ @conn.exec_params(sql, args) do |rs|
+ rs.each do |row|
+ ids.push(row['id'])
+ end
+ end
+ return ids
+ end
+
+ def exec_update(sql, args)
+ begin
+ rs = @conn.exec_params(sql, args)
+ rescue Exception => e
+ puts sql + ": " + args.inspect()
+ puts e.message
+ puts $@
+ ensure
+ rs.clear if rs
+ end
+ 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 age,genre,grouping,code,descr FROM Series WHERE id=$1;"
+ args = [id]
+ @conn.exec_params(sql, args) do |rs|
+ if rs.ntuples > 0
+ row = rs[0]
+ series = Series.new(id)
+ series.age = row['age']
+ series.genre = row['genre']
+ series.grouping = row['grouping']
+ series.code = row['code']
+ series.descr = row['descr']
+ return series
+ end
+ end
+ return nil
+ end
+
+ def populate_classifications_table
+ puts "Populating the Classifications table..."
+ first = true
+ CSV.foreach(@basePath + '/csv/class.csv') do |row|
+ if first
+ # skip the header row
+ first = false
+ else
+
+ # First, add a row to the Classifications table
+
+ id = next_id('classification_id')
+ ddc = row[0]
+ lcc = row[1]
+ author_grouping = row[2]
+ author_sort = row[3]
+ title_grouping = row[4]
+ title = row[5]
+
+ sqlInsert = "INSERT INTO Classifications (id, ddc, lcc, author_grouping, author_sort, title_grouping, title) VALUES ($1, $2, $3, $4, $5, $6, $7);"
+ args = [id, ddc, lcc, author_grouping, author_sort, title_grouping, title]
+ exec_update(sqlInsert, args)
+
+ # Second, link up with the appropriate FAST table entries
+
+ fast = []
+ input = row[6]
+ if input.length > 0
+ fast = input.split(';')
+ end
+
+ fast.each do |fast_id|
+ sqlInsert = "INSERT INTO FAST_Classifications (fast, classification) VALUES ($1, $2);"
+ args = [fast_id, id]
+ exec_update(sqlInsert, args)
+ end
+ end
+ end
+ end
+
+ def populate_fast_table
+ puts "Populating the FAST table..."
+ first = true
+ CSV.foreach(@basePath + '/csv/fast.csv') do |row|
+ if first
+ first = false # skip the header row
+ else
+ id = row[0]
+ descr = row[1]
+ sqlInsert = "INSERT INTO FAST (id, descr) VALUES ($1, $2);"
+ exec_update(sqlInsert, [id, descr])
+ end
+ end
+ end
+
+ def populate_lists_table
+ puts "Populating the Lists table..."
+
+ CSV.foreach(@basePath + "/csv/lists.csv", headers: true) do |row|
+ author_ids = find_all_authors(row['author'])
+ if author_ids.empty?
+ specification = [row['age'], row['category'], row['code'], row['year'], row['author'], row['title']]
+ .map { |x| x.inspect }
+ .join(', ')
+
+ puts "WARNING: For list entry (#{specification}), no such author was found."
+
+ next
+ end
+
+ sqlInsert = %Q(
+ INSERT INTO Lists (id, age, category, code, year, author, title)
+ VALUES ($1, $2, $3, $4, $5, $6, $7);
+ )
+ author_ids.each do |author_id|
+ list_id = next_id('list_id')
+ args = [list_id, row['age'], row['category'], row['code'], row['year'], author_id, row['title']]
+ exec_update(sqlInsert, args)
+
+ update_lists_books_table(list_id, author_id, row['title'])
+ end
+ end
+ end
+
+ # Scan for books that match this Lists entry, and add any matches to the Lists_Books associative table
+ def update_lists_books_table(list_id, author_id, title)
+ title_pattern = Book.grouping_for_title(title).gsub('_', '%')
+ sqlSelect = "SELECT id FROM Books WHERE author = $1 AND title LIKE $2;"
+ args = [author_id, title_pattern]
+
+ @conn.exec_params(sqlSelect, args) do |rs|
+ rs.each do |row|
+ sqlInsert = "INSERT INTO Lists_Books (list, book) VALUES ($1, $2)"
+ args = [list_id, row['id']]
+ exec_update(sqlInsert, args)
+ end
+ end
+ 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
+ exec_update(sqlInsert, args)
+ 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
+ return exec_id_query(sql, [pattern])
+ end
+
+ def query_books_by_ddc
+ sql =
+<<EOS
+ SELECT b.id FROM Classifications c
+ INNER JOIN Books b ON b.classification=c.id
+ ORDER BY c.ddc
+EOS
+ return exec_id_query(sql, [])
+ end
+
+ def query_books_by_series_id(id)
+ sql =
+<<EOS
+ SELECT b.id FROM Books b
+ WHERE b.series = $1
+ ORDER BY b.volume,b.title
+EOS
+ return exec_id_query(sql, [id])
+ end
+
+ def query_series_by_age(pattern)
+ sql =
+<<EOS
+ SELECT s.id
+ FROM Series s
+ WHERE s.age LIKE $1
+ ORDER BY s.grouping,s.descr
+EOS
+ return exec_id_query(sql, [pattern])
+ end
end
projects / quanlib.git / blobdiff