10 @basepath = '/arc/quanlib' # TODO: FIXME: configure this in a sane way
13 #@dburl = 'dbi:Pg:quanlib:localhost'
22 # @conn = PGconn.connect('localhost', 5432, '', '', 'quanlib', 'quanlib', 'quanlib')
23 @conn = PG.connect('localhost', 5432, '', '', 'quanlib', 'quanlib', 'quanlib')
31 def construct_efs_path(efs_id)
32 id_str = sprintf('%010d', efs_id)
33 path = sprintf('%s/%s/%s/%s', id_str[0,2], id_str[2,2], id_str[4,2], id_str[6,2])
34 name = id_str + '.dat'
41 CREATE TABLE Authors (
42 id INTEGER PRIMARY KEY,
52 id INTEGER PRIMARY KEY,
53 author INTEGER REFERENCES Authors(id),
54 classification INTEGER REFERENCES Classifications(id),
58 series INTEGER REFERENCES Series(id),
64 create_classification =
66 CREATE TABLE Classifications (
67 id INTEGER PRIMARY KEY,
70 author_grouping VARCHAR(64),
71 author_sort VARCHAR(128),
72 title_grouping VARCHAR(256),
88 id VARCHAR(32) PRIMARY KEY,
93 # Associative entity, linking FAST and Classifications tables
94 # in a 0..n to 0..m relationship
95 create_fast_classifications =
97 CREATE TABLE FAST_Classifications (
98 fast VARCHAR(32) REFERENCES FAST(id),
99 classification INTEGER REFERENCES Classifications(id)
105 CREATE TABLE Series (
106 id INTEGER PRIMARY KEY,
109 grouping VARCHAR(64),
117 create_classification,
122 create_fast_classifications,
123 'CREATE SEQUENCE author_id;',
124 'CREATE SEQUENCE book_id;',
125 'CREATE SEQUENCE classification_id;',
126 'CREATE SEQUENCE efs_id;',
127 'CREATE SEQUENCE series_id;'
134 populate_fast_table()
135 populate_classifications_table()
136 populate_series_table()
142 'DROP TABLE FAST_Classifications;',
143 'DROP TABLE Authors;',
144 'DROP TABLE Classifications;',
147 'DROP TABLE Series;',
148 'DROP SEQUENCE author_id;',
149 'DROP SEQUENCE book_id;',
150 'DROP SEQUENCE classification_id;',
151 'DROP SEQUENCE efs_id;',
152 'DROP SEQUENCE series_id;'
158 rescue Exception => exc
159 puts 'WARNING: "' + stmt + '" failed: ' + exc.to_s
164 def find_author(author)
165 sqlSelect = "SELECT id FROM Authors WHERE grouping=$1 AND reading=$2 AND sort=$3;"
166 args = [author.grouping, author.reading_order, author.sort_order]
167 @conn.exec_params(sqlSelect, args) do |rs|
176 sql = "SELECT 1 FROM pg_tables WHERE tableowner='quanlib' AND tablename='books'"
178 @conn.exec(sql).each do |row|
188 #puts 'DEBUG: load_author(' + id + ')'
189 sqlSelect = "SELECT grouping, reading, sort FROM Authors WHERE id=$1"
191 @conn.exec_params(sqlSelect, args) do |rs|
193 raise "Expected 1 row for " + id + " but got " + rs.ntuples + ": " + sqlSelect
196 author = Author.new(row['grouping'], row['reading'], row['sort'])
197 #puts 'DEBUG: author: ' + author.inspect()
200 #puts 'DEBUG: NOT FOUND'
204 def store_author(author)
205 id = find_author(author)
207 id = next_id('author_id')
208 sqlInsert = "INSERT INTO Authors(id, grouping, reading, sort) VALUES ($1, $2, $3, $4);"
209 args = [id, author.grouping, author.reading_order, author.sort_order]
211 rs = @conn.exec_params(sqlInsert, args)
212 rescue Exception => e
213 puts sqlInsert + ": " + args.inspect()
224 #puts 'DEBUG: load_book(' + id + ')'
225 sql = "SELECT author, classification, cover, description, path, series, title, volume FROM Books WHERE id=$1;"
229 @conn.exec_params(sql, [id]) do |rs|
231 raise 'Expected one row in Books for id ' + id + ', but found ' + rs.length + '.'
236 book = Book.new(self)
237 book.author = load_author(row['author'])
238 book.classification_id = row['classification']
239 book.cover = load_cover(row['cover'])
240 book.description = row['description']
241 book.path = row['path']
242 book.series_id = row['series']
243 book.title = row['title']
244 book.volume = row['volume']
246 rescue Exception => e
252 #puts 'DEBUG: loaded book: ' + book.inspect()
257 sql = "INSERT INTO Books (id, author, classification, cover, description, path, series, title, volume) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9);"
259 book_id = next_id('book_id')
261 author_id = store_author(book.author)
262 (efs_id, mime_type) = store_cover(book)
264 args = [book_id, author_id, book.classification_id, efs_id, book.description(), book.path(), book.series_id(), book.title(), book.volume()]
267 rs = @conn.exec_params(sql, args)
268 rescue Exception => e
269 puts sql + ": " + args.inspect()
279 def find_classification(author_grouping, title_grouping)
280 #puts 'find_classification("' + author_grouping.inspect + '", "' + title_grouping.inspect + '")...'
281 sql = "SELECT id FROM Classifications WHERE author_grouping = $1 AND title_grouping = $2;"
282 @conn.exec_params(sql, [author_grouping, title_grouping]) do |rs|
284 #puts ' --> ' + rs[0]['id'].inspect
292 def load_classification(id)
293 sql = "SELECT ddc, lcc, author_grouping, author_sort, title_grouping, title "
294 sql += " FROM Classifications WHERE id=$1"
295 @conn.exec_params(sql, [id]) do |rs|
300 author_grouping = row['author_grouping']
301 author = row['author_sort']
302 title_grouping = row['title_grouping']
305 result = Classification.new(ddc, lcc, author_grouping, author, title_grouping, title)
319 mime_type = 'application/octet-stream'
321 sql = "SELECT mimeType FROM Efs WHERE id=$1"
322 @conn.exec_params(sql, [id]) do |rs|
324 raise "Expected one row but got " + rs.ntuples + ": " + sql + ": " + id
326 mime_type = rs[0]['mimeType']
329 (efspath, efsname) = construct_efs_path(id)
331 fullpath = @basepath + '/efs/' + efspath + '/' + efsname
333 return Cover.new(nil, fullpath, mime_type)
335 #File.open(fullpath, 'rb') do |is|
336 # return Cover.new(is, fullpath, mime_type)
342 def store_cover(book)
350 @conn.exec("SELECT nextval('efs_id')") do |rs|
351 efs_id = rs[0]['nextval']
358 (efspath, efsname) = construct_efs_path(efs_id)
360 efspath = @basepath + '/efs/' + efspath
362 FileUtils.mkdir_p(efspath)
364 (filepath, mimetype) = cover.write_image(efspath, efsname)
366 sql = "INSERT INTO efs VALUES ($1, $2)"
368 rs = @conn.exec_params(sql, [efs_id, mimetype])
369 rescue Exception => e
370 puts sql + ": " + efs_id + ", " + mimetype
377 return efs_id, mimetype
380 def exec_id_query(sql, args)
382 @conn.exec_params(sql, args) do |rs|
390 def exec_update(sql, args)
392 rs = @conn.exec_params(sql, args)
393 rescue Exception => e
394 puts sql + ": " + args.inspect()
402 def next_id(seq_name)
404 @conn.exec("SELECT nextval('" + seq_name + "');") do |rs|
405 id = rs[0]['nextval']
410 def get_series(grouping, code)
415 sql = "SELECT id FROM Series WHERE grouping=$1 AND code=$2;"
416 args = [grouping, code]
417 @conn.exec_params(sql, args).each do |row|
421 # TODO: Create a new series object here?
422 puts 'WARNING: series("' + grouping + '", "' + code + '") not found.'
427 sql = "SELECT age,genre,grouping,code,descr FROM Series WHERE id=$1;"
429 @conn.exec_params(sql, args) do |rs|
432 series = Series.new(id)
433 series.age = row['age']
434 series.genre = row['genre']
435 series.grouping = row['grouping']
436 series.code = row['code']
437 series.descr = row['descr']
444 def populate_classifications_table
445 puts "Populating the Classifications table..."
447 CSV.foreach(@basepath + '/csv/class.csv') do |row|
449 # skip the header row
453 # First, add a row to the Classifications table
455 id = next_id('classification_id')
458 author_grouping = row[2]
460 title_grouping = row[4]
463 sqlInsert = "INSERT INTO Classifications (id, ddc, lcc, author_grouping, author_sort, title_grouping, title) VALUES ($1, $2, $3, $4, $5, $6, $7);"
464 args = [id, ddc, lcc, author_grouping, author_sort, title_grouping, title]
465 exec_update(sqlInsert, args)
467 # Second, link up with the appropriate FAST table entries
472 fast = input.split(';')
475 fast.each do |fast_id|
476 sqlInsert = "INSERT INTO FAST_Classifications (fast, classification) VALUES ($1, $2);"
478 exec_update(sqlInsert, args)
484 def populate_fast_table
485 puts "Populating the FAST table..."
487 CSV.foreach(@basepath + '/csv/fast.csv') do |row|
489 first = false # skip the header row
493 sqlInsert = "INSERT INTO FAST (id, descr) VALUES ($1, $2);"
494 exec_update(sqlInsert, [id, descr])
499 def populate_series_table
500 puts "Populating the Series table..."
501 CSV.foreach(@basepath + '/csv/series.csv') do |row|
502 id = next_id('series_id')
503 sqlInsert = "INSERT INTO Series (id, age, genre, grouping, code, descr) VALUES ($1, $2, $3, $4, $5, $6);"
505 exec_update(sqlInsert, args)
509 def query_books_by_author(pattern)
512 SELECT b.id FROM Authors a
513 INNER JOIN Books b ON b.author=a.id
514 LEFT OUTER JOIN Series s on s.id=b.series
515 WHERE upper(a.grouping) LIKE $1
516 ORDER BY a.grouping, b.series, b.volume, b.title
518 return exec_id_query(sql, [pattern])
521 def query_books_by_ddc
524 SELECT b.id FROM Classifications c
525 INNER JOIN Books b ON b.classification=c.id
528 return exec_id_query(sql, [])
531 def query_books_by_series_id(id)
534 SELECT b.id FROM Books b
536 ORDER BY b.volume,b.title
538 return exec_id_query(sql, [id])
541 def query_series_by_age(pattern)
547 ORDER BY s.grouping,s.descr
549 return exec_id_query(sql, [pattern])