12 @basePath + '/csv/unclassified.csv'
15 def initialize(config_file)
18 config = IniFile.load(config_file)
20 puts 'FATAL: Failed to load config file "' + config_file + '". Aborting initialization.'
24 section = config['database']
25 @dbhost = section['host']
27 @dbname = section['name']
28 @dbuser = section['user']
29 @dbpass = section['pass']
31 section = config['filesystem']
32 @basePath = section['basePath']
36 # @conn = PGconn.connect('localhost', 5432, '', '', 'quanlib', 'quanlib', 'quanlib')
37 # @conn = PG.connect(@dbhost, @dbport, '', '', @dbname, @dbuser, @dbpass)
38 @conn = TimedConn.new(PG.connect(@dbhost, @dbport, '', '', @dbname, @dbuser, @dbpass))
46 def construct_efs_path(efs_id)
47 id_str = sprintf('%010d', efs_id)
48 path = sprintf('%s/%s/%s/%s', id_str[0,2], id_str[2,2], id_str[4,2], id_str[6,2])
49 name = id_str + '.dat'
53 def create_schema(skip_class)
56 CREATE TABLE Authors (
57 id INTEGER PRIMARY KEY,
67 id INTEGER PRIMARY KEY,
68 author INTEGER REFERENCES Authors(id),
69 classification INTEGER REFERENCES Classifications(id),
73 series INTEGER REFERENCES Series(id),
79 create_classification =
81 CREATE TABLE Classifications (
82 id INTEGER PRIMARY KEY,
85 author_grouping VARCHAR(64),
86 author_sort VARCHAR(128),
87 title_grouping VARCHAR(256),
95 id INTEGER PRIMARY KEY,
103 id VARCHAR(32) PRIMARY KEY,
108 # Associative entity, linking FAST and Classifications tables
109 # in a 0..n to 0..m relationship
110 create_fast_classifications =
112 CREATE TABLE FAST_Classifications (
113 fast VARCHAR(32) REFERENCES FAST(id),
114 classification INTEGER REFERENCES Classifications(id)
120 CREATE TABLE Series (
121 id INTEGER PRIMARY KEY,
124 grouping VARCHAR(64),
132 create_classification,
137 create_fast_classifications,
138 'CREATE SEQUENCE author_id;',
139 'CREATE SEQUENCE book_id;',
140 'CREATE SEQUENCE classification_id;',
141 'CREATE SEQUENCE efs_id;',
142 'CREATE SEQUENCE series_id;'
149 if skip_class == false
150 populate_fast_table()
151 populate_classifications_table()
154 populate_series_table()
161 'DROP TABLE FAST_Classifications;',
162 'DROP TABLE Authors;',
163 'DROP TABLE Classifications;',
166 'DROP TABLE Series;',
167 'DROP SEQUENCE author_id;',
168 'DROP SEQUENCE book_id;',
169 'DROP SEQUENCE classification_id;',
170 'DROP SEQUENCE efs_id;',
171 'DROP SEQUENCE series_id;'
177 rescue Exception => exc
178 puts 'WARNING: "' + stmt + '" failed: ' + exc.to_s
183 def find_author(author)
184 sqlSelect = "SELECT id FROM Authors WHERE grouping=$1 AND reading=$2 AND sort=$3;"
185 args = [author.grouping, author.reading_order, author.sort_order]
186 @conn.exec_params(sqlSelect, args) do |rs|
194 def init_db(skip_class)
195 sql = "SELECT 1 FROM pg_tables WHERE tableowner='quanlib' AND tablename='books'"
197 @conn.exec(sql).each do |row|
202 create_schema(skip_class)
207 sqlSelect = "SELECT grouping, reading, sort FROM Authors WHERE id=$1"
209 @conn.exec_params(sqlSelect, args) do |rs|
211 raise "Expected 1 row for " + id + " but got " + rs.ntuples + ": " + sqlSelect
214 author = Author.new(row['grouping'], row['reading'], row['sort'])
220 def store_author(author)
221 id = find_author(author)
223 id = next_id('author_id')
224 sqlInsert = "INSERT INTO Authors(id, grouping, reading, sort) VALUES ($1, $2, $3, $4);"
225 args = [id, author.grouping, author.reading_order, author.sort_order]
227 rs = @conn.exec_params(sqlInsert, args)
228 rescue Exception => e
229 puts sqlInsert + ": " + args.inspect()
240 sql = "SELECT author, classification, cover, description, path, series, title, volume FROM Books WHERE id=$1;"
244 @conn.exec_params(sql, [id]) do |rs|
246 raise 'Expected one row in Books for id ' + id + ', but found ' + rs.length + '.'
251 book = Book.new(self)
252 book.author = load_author(row['author'])
253 book.classification_id = row['classification']
254 book.cover = load_cover(row['cover'])
255 book.description = row['description']
256 book.path = row['path']
257 book.series_id = row['series']
258 book.title = row['title']
259 book.volume = row['volume']
261 rescue Exception => e
271 sql = "INSERT INTO Books (id, author, classification, cover, description, path, series, title, volume) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9);"
273 book_id = next_id('book_id')
275 author_id = store_author(book.author)
276 (efs_id, mime_type) = store_cover(book)
278 args = [book_id, author_id, book.classification_id, efs_id, book.description(), book.path(), book.series_id(), book.title(), book.volume()]
281 rs = @conn.exec_params(sql, args)
282 rescue Exception => e
283 puts sql + ": " + args.inspect()
293 def find_classification(author_grouping, title_grouping)
294 #puts 'find_classification("' + author_grouping.inspect + '", "' + title_grouping.inspect + '")...'
295 sql = "SELECT id FROM Classifications WHERE author_grouping = $1 AND title_grouping = $2;"
296 @conn.exec_params(sql, [author_grouping, title_grouping]) do |rs|
298 #puts ' --> ' + rs[0]['id'].inspect
306 def load_classification(id)
307 sql = "SELECT ddc, lcc, author_grouping, author_sort, title_grouping, title "
308 sql += " FROM Classifications WHERE id=$1"
309 @conn.exec_params(sql, [id]) do |rs|
314 author_grouping = row['author_grouping']
315 author = row['author_sort']
316 title_grouping = row['title_grouping']
319 result = Classification.new(ddc, lcc, author_grouping, author, title_grouping, title)
333 mime_type = 'application/octet-stream'
335 sql = "SELECT mimeType FROM Efs WHERE id=$1"
336 @conn.exec_params(sql, [id]) do |rs|
338 raise "Expected one row but got " + rs.ntuples + ": " + sql + ": " + id
340 mime_type = rs[0]['mimeType']
343 (efspath, efsname) = construct_efs_path(id)
345 fullpath = @basePath + '/efs/' + efspath + '/' + efsname
347 return Cover.new(nil, fullpath, mime_type)
349 #File.open(fullpath, 'rb') do |is|
350 # return Cover.new(is, fullpath, mime_type)
356 def store_cover(book)
364 @conn.exec("SELECT nextval('efs_id')") do |rs|
365 efs_id = rs[0]['nextval']
372 (efspath, efsname) = construct_efs_path(efs_id)
374 efspath = @basePath + '/efs/' + efspath
376 FileUtils.mkdir_p(efspath)
378 (filepath, mimetype) = cover.write_image(efspath, efsname)
380 sql = "INSERT INTO efs VALUES ($1, $2)"
382 rs = @conn.exec_params(sql, [efs_id, mimetype])
383 rescue Exception => e
384 puts sql + ": " + efs_id + ", " + mimetype
391 return efs_id, mimetype
394 def exec_id_query(sql, args)
396 @conn.exec_params(sql, args) do |rs|
404 def exec_update(sql, args)
406 rs = @conn.exec_params(sql, args)
407 rescue Exception => e
408 puts sql + ": " + args.inspect()
416 def next_id(seq_name)
418 @conn.exec("SELECT nextval('" + seq_name + "');") do |rs|
419 id = rs[0]['nextval']
424 def get_series(grouping, code)
429 sql = "SELECT id FROM Series WHERE grouping=$1 AND code=$2;"
430 args = [grouping, code]
431 @conn.exec_params(sql, args).each do |row|
435 # TODO: Create a new series object here?
436 puts 'WARNING: series("' + grouping + '", "' + code + '") not found.'
441 sql = "SELECT age,genre,grouping,code,descr FROM Series WHERE id=$1;"
443 @conn.exec_params(sql, args) do |rs|
446 series = Series.new(id)
447 series.age = row['age']
448 series.genre = row['genre']
449 series.grouping = row['grouping']
450 series.code = row['code']
451 series.descr = row['descr']
458 def populate_classifications_table
459 puts "Populating the Classifications table..."
461 CSV.foreach(@basePath + '/csv/class.csv') do |row|
463 # skip the header row
467 # First, add a row to the Classifications table
469 id = next_id('classification_id')
472 author_grouping = row[2]
474 title_grouping = row[4]
477 sqlInsert = "INSERT INTO Classifications (id, ddc, lcc, author_grouping, author_sort, title_grouping, title) VALUES ($1, $2, $3, $4, $5, $6, $7);"
478 args = [id, ddc, lcc, author_grouping, author_sort, title_grouping, title]
479 exec_update(sqlInsert, args)
481 # Second, link up with the appropriate FAST table entries
486 fast = input.split(';')
489 fast.each do |fast_id|
490 sqlInsert = "INSERT INTO FAST_Classifications (fast, classification) VALUES ($1, $2);"
492 exec_update(sqlInsert, args)
498 def populate_fast_table
499 puts "Populating the FAST table..."
501 CSV.foreach(@basePath + '/csv/fast.csv') do |row|
503 first = false # skip the header row
507 sqlInsert = "INSERT INTO FAST (id, descr) VALUES ($1, $2);"
508 exec_update(sqlInsert, [id, descr])
513 def populate_series_table
514 puts "Populating the Series table..."
515 CSV.foreach(@basePath + '/csv/series.csv') do |row|
516 id = next_id('series_id')
517 sqlInsert = "INSERT INTO Series (id, age, genre, grouping, code, descr) VALUES ($1, $2, $3, $4, $5, $6);"
519 exec_update(sqlInsert, args)
523 def query_books_by_author(pattern)
526 SELECT b.id FROM Authors a
527 INNER JOIN Books b ON b.author=a.id
528 LEFT OUTER JOIN Series s on s.id=b.series
529 WHERE upper(a.grouping) LIKE $1
530 ORDER BY a.grouping, b.series, b.volume, b.title
532 return exec_id_query(sql, [pattern])
535 def query_books_by_ddc
538 SELECT b.id FROM Classifications c
539 INNER JOIN Books b ON b.classification=c.id
542 return exec_id_query(sql, [])
545 def query_books_by_series_id(id)
548 SELECT b.id FROM Books b
550 ORDER BY b.volume,b.title
552 return exec_id_query(sql, [id])
555 def query_series_by_age(pattern)
561 ORDER BY s.grouping,s.descr
563 return exec_id_query(sql, [pattern])