8 @basepath = '/arc/quanlib' # TODO: FIXME: configure this in a sane way
11 #@dburl = 'dbi:Pg:quanlib:localhost'
20 # @conn = PGconn.connect('localhost', 5432, '', '', 'quanlib', 'quanlib', 'quanlib')
21 @conn = PG.connect('localhost', 5432, '', '', 'quanlib', 'quanlib', 'quanlib')
29 def construct_efs_path(efs_id)
30 id_str = sprintf('%010d', efs_id)
31 path = sprintf('%s/%s/%s/%s', id_str[0,2], id_str[2,2], id_str[4,2], id_str[6,2])
32 name = id_str + '.dat'
39 CREATE TABLE Authors (
40 id INTEGER PRIMARY KEY,
50 id INTEGER PRIMARY KEY,
51 author INTEGER REFERENCES Authors(id),
55 series INTEGER REFERENCES Series(id),
72 id INTEGER PRIMARY KEY,
86 'CREATE SEQUENCE author_id;',
87 'CREATE SEQUENCE book_id;',
88 'CREATE SEQUENCE efs_id;',
89 'CREATE SEQUENCE series_id;'
96 populate_series_table()
102 'DROP TABLE Authors;',
104 'DROP TABLE Series;',
105 'DROP SEQUENCE author_id;',
106 'DROP SEQUENCE book_id;',
107 'DROP SEQUENCE efs_id;',
108 'DROP SEQUENCE series_id;'
116 def find_author(author)
117 sqlSelect = "SELECT id FROM Authors WHERE grouping=$1 AND reading=$2 AND sort=$3;"
118 args = [author.grouping, author.reading_order, author.sort_order]
119 @conn.exec_params(sqlSelect, args) do |rs|
128 sql = "SELECT 1 FROM pg_tables WHERE tableowner='quanlib' AND tablename='books'"
130 @conn.exec(sql).each do |row|
140 #puts 'DEBUG: load_author(' + id + ')'
141 sqlSelect = "SELECT grouping, reading, sort FROM Authors WHERE id=$1"
143 @conn.exec_params(sqlSelect, args) do |rs|
145 raise "Expected 1 row for " + id + " but got " + rs.ntuples + ": " + sqlSelect
148 author = Author.new(row['grouping'], row['reading'], row['sort'])
149 #puts 'DEBUG: author: ' + author.inspect()
152 #puts 'DEBUG: NOT FOUND'
156 def store_author(author)
157 id = find_author(author)
159 id = next_id('author_id')
160 sqlInsert = "INSERT INTO Authors(id, grouping, reading, sort) VALUES ($1, $2, $3, $4);"
161 args = [id, author.grouping, author.reading_order, author.sort_order]
163 rs = @conn.exec_params(sqlInsert, args)
164 rescue Exception => e
165 puts sqlInsert + ": " + args.inspect()
176 #puts 'DEBUG: load_book(' + id + ')'
177 sql = "SELECT author, cover, description, path, series, title, volume FROM Books WHERE id=$1;"
181 @conn.exec_params(sql, [id]) do |rs|
183 raise 'Expected one row in Books for id ' + id + ', but found ' + rs.length + '.'
188 book = Book.new(self)
189 book.author = load_author(row['author'])
190 book.cover = load_cover(row['cover'])
191 book.description = row['description']
192 book.path = row['path']
193 book.series_id = row['series']
194 book.title = row['title']
195 book.volume = row['volume']
197 rescue Exception => e
203 #puts 'DEBUG: loaded book: ' + book.inspect()
208 sql = "INSERT INTO Books (id, author, cover, description, path, series, title, volume) VALUES ($1, $2, $3, $4, $5, $6, $7, $8);"
210 book_id = next_id('book_id')
212 author_id = store_author(book.author)
213 (efs_id, mime_type) = store_cover(book)
215 args = [book_id, author_id, efs_id, book.description(), book.path(), book.series_id(), book.title(), book.volume()]
218 rs = @conn.exec_params(sql, args)
219 rescue Exception => e
220 puts sql + ": " + args.inspect()
235 mime_type = 'application/octet-stream'
237 sql = "SELECT mimeType FROM Efs WHERE id=$1"
238 @conn.exec_params(sql, [id]) do |rs|
240 raise "Expected one row but got " + rs.ntuples + ": " + sql + ": " + id
242 mime_type = rs[0]['mimeType']
245 (efspath, efsname) = construct_efs_path(id)
247 File.open(@basepath + '/efs/' + efspath + '/' + efsname, 'rb') do |is|
248 return Cover.new(is, efsname, mime_type)
254 def store_cover(book)
262 @conn.exec("SELECT nextval('efs_id')") do |rs|
263 efs_id = rs[0]['nextval']
270 (efspath, efsname) = construct_efs_path(efs_id)
272 efspath = @basepath + '/efs/' + efspath
274 FileUtils.mkdir_p(efspath)
276 (filepath, mimetype) = cover.write_image(efspath, efsname)
278 sql = "INSERT INTO efs VALUES ($1, $2)"
280 rs = @conn.exec_params(sql, [efs_id, mimetype])
281 rescue Exception => e
282 puts sql + ": " + efs_id + ", " + mimetype
289 return efs_id, mimetype
292 def next_id(seq_name)
294 @conn.exec("SELECT nextval('" + seq_name + "');") do |rs|
295 id = rs[0]['nextval']
300 def get_series(grouping, code)
305 sql = "SELECT id FROM Series WHERE grouping=$1 AND code=$2;"
306 args = [grouping, code]
307 @conn.exec_params(sql, args).each do |row|
311 # TODO: Create a new series object here?
312 puts 'WARNING: series("' + grouping + '", "' + code + '") not found.'
317 sql = "SELECT descr FROM Series WHERE id=$1;"
319 @conn.exec_params(sql, args) do |rs|
321 return rs[0]['descr']
327 def populate_series_table
328 puts "Populating the Series table..."
329 CSV.foreach(@basepath + '/csv/series.csv') do |row|
330 id = next_id('series_id')
331 sqlInsert = "INSERT INTO Series (id, age, genre, grouping, code, descr) VALUES ($1, $2, $3, $4, $5, $6);"
334 # DEBUG: puts 'SQL> ' + sqlInsert + ': ' + args.inspect()
335 rs = @conn.exec_params(sqlInsert, args)
336 rescue Exception => e
337 puts sqlInsert + ": " + args.inspect()
346 def query_books_by_author(pattern)
349 SELECT b.id FROM Authors a
350 INNER JOIN Books b ON b.author=a.id
351 LEFT OUTER JOIN Series s on s.id=b.series
352 WHERE upper(a.grouping) LIKE $1
353 ORDER BY a.grouping, b.series, b.volume, b.title
356 @conn.exec_params(sql, [pattern]) do |rs|
358 book_ids.push(row['id'])