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),
57 series INTEGER REFERENCES Series(id),
74 id INTEGER PRIMARY KEY,
88 'CREATE SEQUENCE author_id;',
89 'CREATE SEQUENCE book_id;',
90 'CREATE SEQUENCE efs_id;',
91 'CREATE SEQUENCE series_id;'
98 populate_series_table()
104 'DROP TABLE Authors;',
106 'DROP TABLE Series;',
107 'DROP SEQUENCE author_id;',
108 'DROP SEQUENCE book_id;',
109 'DROP SEQUENCE efs_id;',
110 'DROP SEQUENCE series_id;'
118 def find_author(author)
119 sqlSelect = "SELECT id FROM Authors WHERE grouping=$1 AND reading=$2 AND sort=$3;"
120 args = [author.grouping, author.reading_order, author.sort_order]
121 @conn.exec_params(sqlSelect, args) do |rs|
130 sql = "SELECT 1 FROM pg_tables WHERE tableowner='quanlib' AND tablename='books'"
132 @conn.exec(sql).each do |row|
142 #puts 'DEBUG: load_author(' + id + ')'
143 sqlSelect = "SELECT grouping, reading, sort FROM Authors WHERE id=$1"
145 @conn.exec_params(sqlSelect, args) do |rs|
147 raise "Expected 1 row for " + id + " but got " + rs.ntuples + ": " + sqlSelect
150 author = Author.new(row['grouping'], row['reading'], row['sort'])
151 #puts 'DEBUG: author: ' + author.inspect()
154 #puts 'DEBUG: NOT FOUND'
158 def store_author(author)
159 id = find_author(author)
161 id = next_id('author_id')
162 sqlInsert = "INSERT INTO Authors(id, grouping, reading, sort) VALUES ($1, $2, $3, $4);"
163 args = [id, author.grouping, author.reading_order, author.sort_order]
165 rs = @conn.exec_params(sqlInsert, args)
166 rescue Exception => e
167 puts sqlInsert + ": " + args.inspect()
178 #puts 'DEBUG: load_book(' + id + ')'
179 sql = "SELECT author, cover, description, path, series, title, volume FROM Books WHERE id=$1;"
183 @conn.exec_params(sql, [id]) do |rs|
185 raise 'Expected one row in Books for id ' + id + ', but found ' + rs.length + '.'
190 book = Book.new(self)
191 book.author = load_author(row['author'])
192 book.cover = load_cover(row['cover'])
193 book.description = row['description']
194 book.path = row['path']
195 book.series_id = row['series']
196 book.title = row['title']
197 book.volume = row['volume']
199 rescue Exception => e
205 #puts 'DEBUG: loaded book: ' + book.inspect()
210 sql = "INSERT INTO Books (id, author, cover, description, path, series, title, volume) VALUES ($1, $2, $3, $4, $5, $6, $7, $8);"
212 book_id = next_id('book_id')
214 author_id = store_author(book.author)
215 (efs_id, mime_type) = store_cover(book)
217 args = [book_id, author_id, efs_id, book.description(), book.path(), book.series_id(), book.title(), book.volume()]
220 rs = @conn.exec_params(sql, args)
221 rescue Exception => e
222 puts sql + ": " + args.inspect()
237 mime_type = 'application/octet-stream'
239 sql = "SELECT mimeType FROM Efs WHERE id=$1"
240 @conn.exec_params(sql, [id]) do |rs|
242 raise "Expected one row but got " + rs.ntuples + ": " + sql + ": " + id
244 mime_type = rs[0]['mimeType']
247 (efspath, efsname) = construct_efs_path(id)
249 fullpath = @basepath + '/efs/' + efspath + '/' + efsname
251 return Cover.new(nil, fullpath, mime_type)
253 #File.open(fullpath, 'rb') do |is|
254 # return Cover.new(is, fullpath, mime_type)
260 def store_cover(book)
268 @conn.exec("SELECT nextval('efs_id')") do |rs|
269 efs_id = rs[0]['nextval']
276 (efspath, efsname) = construct_efs_path(efs_id)
278 efspath = @basepath + '/efs/' + efspath
280 FileUtils.mkdir_p(efspath)
282 (filepath, mimetype) = cover.write_image(efspath, efsname)
284 sql = "INSERT INTO efs VALUES ($1, $2)"
286 rs = @conn.exec_params(sql, [efs_id, mimetype])
287 rescue Exception => e
288 puts sql + ": " + efs_id + ", " + mimetype
295 return efs_id, mimetype
298 def next_id(seq_name)
300 @conn.exec("SELECT nextval('" + seq_name + "');") do |rs|
301 id = rs[0]['nextval']
306 def get_series(grouping, code)
311 sql = "SELECT id FROM Series WHERE grouping=$1 AND code=$2;"
312 args = [grouping, code]
313 @conn.exec_params(sql, args).each do |row|
317 # TODO: Create a new series object here?
318 puts 'WARNING: series("' + grouping + '", "' + code + '") not found.'
323 sql = "SELECT age,genre,grouping,code,descr FROM Series WHERE id=$1;"
325 @conn.exec_params(sql, args) do |rs|
328 series = Series.new(id)
329 series.age = row['age']
330 series.genre = row['genre']
331 series.grouping = row['grouping']
332 series.code = row['code']
333 series.descr = row['descr']
340 def populate_series_table
341 puts "Populating the Series table..."
342 CSV.foreach(@basepath + '/csv/series.csv') do |row|
343 id = next_id('series_id')
344 sqlInsert = "INSERT INTO Series (id, age, genre, grouping, code, descr) VALUES ($1, $2, $3, $4, $5, $6);"
347 # DEBUG: puts 'SQL> ' + sqlInsert + ': ' + args.inspect()
348 rs = @conn.exec_params(sqlInsert, args)
349 rescue Exception => e
350 puts sqlInsert + ": " + args.inspect()
359 def query_books_by_author(pattern)
362 SELECT b.id FROM Authors a
363 INNER JOIN Books b ON b.author=a.id
364 LEFT OUTER JOIN Series s on s.id=b.series
365 WHERE upper(a.grouping) LIKE $1
366 ORDER BY a.grouping, b.series, b.volume, b.title
369 @conn.exec_params(sql, [pattern]) do |rs|
371 book_ids.push(row['id'])
377 def query_books_by_series_id(id)
380 SELECT b.id FROM Books b
382 ORDER BY b.volume,b.title
385 @conn.exec_params(sql, [id]) do |rs|
387 book_ids.push(row['id'])
393 def query_series_by_age(pattern)
396 SELECT s.id FROM Series s
401 @conn.exec_params(sql, [pattern]) do |rs|
403 series_ids.push(row['id'])