+
+require 'fileutils'
+require 'pg'
+
+class Store
+ def initialize
+ @basepath = '/home/chris/prog/quanlib/efs' # TODO: FIXME: configure this in a sane way
+ @conn = nil
+
+ #@dburl = 'dbi:Pg:quanlib:localhost'
+ @dbhost = "localhost"
+ @dbport = 5432
+ @dbname = 'quanlib'
+ @dbuser = 'quanlib'
+ @dbpass = 'quanlib'
+ end
+
+ def connect
+ # @conn = PGconn.connect('localhost', 5432, '', '', 'quanlib', 'quanlib', 'quanlib')
+ @conn = PG.connect('localhost', 5432, '', '', 'quanlib', 'quanlib', 'quanlib')
+ return @conn
+ end
+
+ def disconnect
+ @conn.close()
+ end
+
+ def construct_efs_path(efs_id)
+ id_str = sprintf('%010d', efs_id)
+ path = sprintf('%s/%s/%s/%s', id_str[0,2], id_str[2,2], id_str[4,2], id_str[6,2])
+ name = id_str + '.dat'
+ return path, name
+ end
+
+ def create_schema
+ create_authors =
+<<EOS
+ CREATE TABLE Authors (
+ id SERIAL PRIMARY KEY,
+ grouping VARCHAR(64),
+ reading VARCHAR(128),
+ sort VARCHAR(128)
+ );
+EOS
+
+ create_books =
+<<EOS
+ CREATE TABLE Books (
+ id SERIAL PRIMARY KEY,
+ author INTEGER REFERENCES Authors(id),
+ cover INTEGER,
+ description TEXT,
+ path VARCHAR(256),
+ series VARCHAR(128),
+ title VARCHAR(196),
+ volume VARCHAR(16)
+ );
+EOS
+
+ create_efs =
+<<EOS
+ CREATE TABLE EFS (
+ id INTEGER,
+ mimetype VARCHAR(64)
+ );
+EOS
+
+ stmts = [
+ create_authors,
+ create_books,
+ create_efs,
+ 'CREATE SEQUENCE efs_id;'
+ ]
+
+ for stmt in stmts
+ @conn.exec(stmt)
+ end
+ end
+
+ def dropSchema
+ stmts = [
+ 'DROP TABLE Books;',
+ 'DROP TABLE Authors;',
+ 'DROP TABLE EFS;',
+ 'DROP SEQUENCE efs_id;'
+ ]
+
+ for stmt in stmts do
+ @conn.exec(stmt)
+ end
+ end
+
+ def init_db
+ sql = "SELECT 1 FROM pg_tables WHERE tableowner='quanlib' AND tablename='books'"
+ found = false
+ @conn.exec(sql).each do |row|
+ found = true
+ end
+
+ if ! found
+ create_schema()
+ end
+ 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
+
+ def store_author(author)
+ id = find_author(author)
+ if nil == id
+ sqlInsert = "INSERT INTO Authors(grouping, reading, sort) VALUES ($1, $2, $3);"
+ args = [author.grouping, author.reading_order, author.sort_order]
+ begin
+ rs = @conn.exec_params(sqlInsert, args)
+ rescue Exception => e
+ puts sqlInsert + ": " + args.inspect()
+ puts e.message
+ puts $@
+ ensure
+ rs.clear if rs
+ end
+ end
+ return find_author(author)
+ end
+
+ def store_book(book)
+ sql = "INSERT INTO Books (author, cover, description, path, series, title, volume) VALUES ($1, $2, $3, $4, $5, $6, $7);"
+
+ author_id = store_author(book.author)
+ (efs_id, mime_type) = store_cover(book)
+
+ args = [author_id, efs_id, book.description(), book.path(), book.series(), book.title(), book.volume()]
+
+ begin
+ rs = @conn.exec_params(sql, args)
+ rescue Exception => e
+ puts sql + ": " + args.inspect()
+ puts e.message
+ puts $@
+ ensure
+ rs.clear if rs
+ end
+ end
+
+ def store_cover(book)
+ efs_id = nil
+ cover = book.cover()
+
+ if nil == cover
+ return nil
+ end
+
+ @conn.exec("SELECT nextval('efs_id')") do |rs|
+ efs_id = rs[0]['nextval']
+ end
+
+ if nil == efs_id
+ return nil
+ end
+
+ (efspath, efsname) = construct_efs_path(efs_id)
+
+ efspath = @basepath + '/' + efspath
+
+ FileUtils.mkdir_p(efspath)
+
+ (filepath, mimetype) = cover.writeImage(efspath, efsname)
+
+ sql = "INSERT INTO efs VALUES ($1, $2)"
+ begin
+ rs = @conn.exec_params(sql, [efs_id, mimetype])
+ rescue Exception => e
+ puts e.message
+ puts $@
+ ensure
+ rs.clear if rs
+ end
+
+ return efs_id, mimetype
+ end
+end
+