end
def connect
- # @conn = PGconn.connect('localhost', 5432, '', '', 'quanlib', 'quanlib', 'quanlib')
- # @conn = PG.connect(@dbhost, @dbport, '', '', @dbname, @dbuser, @dbpass)
@conn = TimedConn.new(PG.connect(@dbhost, @dbport, '', '', @dbname, @dbuser, @dbpass))
return @conn
end
return path, name
end
+ 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 =
+ create_authors =
<<EOS
CREATE TABLE Authors (
id INTEGER PRIMARY KEY,
);
EOS
- create_books =
+ create_books =
<<EOS
CREATE TABLE Books (
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),
);
EOS
- create_efs =
+ create_efs =
<<EOS
CREATE TABLE EFS (
id INTEGER PRIMARY KEY,
);
EOS
- create_fast =
+ create_fast =
<<EOS
CREATE TABLE FAST (
id VARCHAR(32) PRIMARY KEY,
);
EOS
- create_series =
+ 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,
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;'
]
end
if skip_class == false
- populate_fast_table()
- populate_classifications_table()
+ populate_fast_table
+ populate_classifications_table
end
- populate_series_table()
-
+ 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 SEQUENCE book_id;',
'DROP SEQUENCE classification_id;',
'DROP SEQUENCE efs_id;',
+ 'DROP SEQUENCE list_id;',
'DROP SEQUENCE series_id;'
]
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 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
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
+ begin
rs = @conn.exec_params(sqlInsert, args)
rescue Exception => e
puts sqlInsert + ": " + args.inspect()
end
def load_book(id)
- sql = "SELECT author, classification, cover, description, path, series, title, volume FROM Books WHERE id=$1;"
+ sql = "SELECT author, classification, cover, description, language, path, series, title, volume FROM Books WHERE id=$1;"
book = nil
begin
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
+ end
rescue Exception => e
puts sql + ": " + id
puts e.message
end
def store_book(book)
- sql = "INSERT INTO Books (id, author, classification, cover, description, path, series, title, volume) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9);"
+ 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 = [book_id, author_id, book.classification_id, efs_id, book.description(), book.path(), book.series_id(), 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
def find_classification(author_grouping, title_grouping)
- #puts 'find_classification("' + author_grouping.inspect + '", "' + title_grouping.inspect + '")...'
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
- #puts ' --> ' + rs[0]['id'].inspect
return rs[0]['id']
end
end
- #puts ' --> NIL'
return nil
end
fullpath = @basePath + '/efs/' + efspath + '/' + efsname
return Cover.new(nil, fullpath, mime_type)
-
- #File.open(fullpath, 'rb') do |is|
- # return Cover.new(is, fullpath, mime_type)
- #end
- #
- #return nil
end
def store_cover(book)
ensure
rs.clear if rs
end
-
+
return efs_id, mimetype
end
id = nil
@conn.exec("SELECT nextval('" + seq_name + "');") do |rs|
id = rs[0]['nextval']
- end
+ end
return id
end
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)
input = row[6]
if input.length > 0
fast = input.split(';')
- end
+ end
fast.each do |fast_id|
sqlInsert = "INSERT INTO FAST_Classifications (fast, classification) VALUES ($1, $2);"
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|
end
def query_books_by_author(pattern)
- sql =
+ sql =
<<EOS
- SELECT b.id FROM Authors a
- INNER JOIN Books b ON b.author=a.id
+ 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
+ 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 =
+ sql =
<<EOS
- SELECT b.id FROM Classifications c
+ SELECT b.id FROM Classifications c
INNER JOIN Books b ON b.classification=c.id
ORDER BY c.ddc
EOS
end
def query_books_by_series_id(id)
- sql =
+ sql =
<<EOS
SELECT b.id FROM Books b
WHERE b.series = $1
end
def query_series_by_age(pattern)
- sql =
+ sql =
<<EOS
- SELECT s.id
+ SELECT s.id
FROM Series s
WHERE s.age LIKE $1
ORDER BY s.grouping,s.descr
projects / quanlib.git / blobdiff