class Poefy::Database

Public Class Methods

connection() click to toggle source

Details for the database connection.

# File lib/poefy/pg.rb, line 19
def self.connection
  PG.connect(
    :dbname   => 'poefy',
    :user     => 'poefy',
    :password => 'poefy'
  )
end
desc(table_name) click to toggle source

Get the description of a table.

# File lib/poefy/pg.rb, line 55
def self.desc table_name
  begin
    sql = "SELECT obj_description($1::regclass, 'pg_class');"
    single_exec!(sql, [table_name]).flatten.first.to_s
  rescue
    ''
  end
end
list() click to toggle source

List all tables in the database. Does not include tables used for testing.

# File lib/poefy/pg.rb, line 43
    def self.list
      rs = Database::single_exec! <<-SQL
        SELECT table_name
        FROM information_schema.tables
        WHERE table_schema = 'public';
      SQL
      rs.flatten.reject do |i|
        i.start_with?('spec_')
      end.sort - ['test']
    end
list_with_desc() click to toggle source

List all database files and their descriptions.

# File lib/poefy/pg.rb, line 65
def self.list_with_desc
  Database::list.map do |i|
    begin
      [i, Database::desc(i)]
    rescue
      [i, '']
    end
  end.to_h
end
single_exec!(sql, sql_args = nil) click to toggle source

Open a class-wide connection, execute a query.

# File lib/poefy/pg.rb, line 28
def self.single_exec! sql, sql_args = nil
  output = nil
  begin
    @@con ||= Database::connection
    output = if sql_args
      @@con.exec(sql, [*sql_args]).values
    else
      @@con.exec(sql).values
    end
  end
  output
end

Public Instance Methods

count() click to toggle source

The number of lines in the table.

# File lib/poefy/pg.rb, line 93
def count
  return 0 if not exists?
  sql = "SELECT COUNT(*) AS num FROM #{table};"
  execute!(sql).first['num'].to_i
end
desc() click to toggle source

Get/set the description of the table.

# File lib/poefy/pg.rb, line 84
def desc
  Database::desc table
end
desc=(description) click to toggle source
# File lib/poefy/pg.rb, line 87
def desc=(description)
  safe_desc = description.to_s.gsub("'","''")
  execute! "COMMENT ON TABLE #{table} IS '#{safe_desc}';"
end
exists?() click to toggle source

See if the table exists or not. Attempt to access table, and return false on error.

# File lib/poefy/pg.rb, line 101
def exists?
  open_connection
  @db.exec("SELECT $1::regclass", [*table])
  true
rescue PG::UndefinedTable
  false
end
rhymes(word, key = nil) click to toggle source

Get all rhyming lines for the word.

# File lib/poefy/pg.rb, line 110
    def rhymes word, key = nil
      return nil if word.nil?

      sql = <<-SQL
        SELECT rhyme, final_word, syllables, line
        FROM #{table}
        WHERE rhyme = $1
        ORDER BY rhyme, final_word, syllables, line
      SQL
      output = word.to_phrase.rhymes.keys.map do |rhyme|
        execute!(sql, [rhyme]).to_a
      end.flatten

      if !key.nil? and %w[rhyme final_word syllables line].include?(key)
        output.map!{ |i| i[key] }
      end
      output
    end
type() click to toggle source

This is the type of database that is being used. It is also used as a signifier that a database has been specified.

# File lib/poefy/pg.rb, line 79
def type
  'pg'
end

Private Instance Methods

create_sprocs() click to toggle source

Create the stored procedures in the database.

# File lib/poefy/pg.rb, line 222
def create_sprocs
  sprocs_sql_hash.each do |key, value|
    db.prepare key.to_s, value
  end
rescue
  msg = "ERROR: Database table structure is invalid." +
      "\n       Please manually DROP the corrupt table and recreate it."
  raise Poefy::StructureInvalid.new(msg)
end
create_table(table_name, description = nil) click to toggle source

Create the table and the index.

# File lib/poefy/pg.rb, line 164
      def create_table table_name, description = nil
        index_name = 'idx_' + table_name
        execute! <<-SQL
          SET client_min_messages TO WARNING;
          DROP INDEX IF EXISTS #{index_name};
          DROP TABLE IF EXISTS #{table_name};
          CREATE TABLE #{table_name} (
            line        TEXT,
            syllables   SMALLINT,
            final_word  TEXT,
            rhyme       TEXT
          );
          CREATE INDEX #{index_name} ON #{table_name} (
            rhyme, final_word, line
          );
        SQL
        self.desc = description
      end
execute!(sql, *args) click to toggle source

Execute a query.

# File lib/poefy/pg.rb, line 147
def execute! sql, *args
  db.exec sql, *args
end
insert_lines(table_name, rows) click to toggle source

Insert an array of lines.

# File lib/poefy/pg.rb, line 152
def insert_lines table_name, rows
  sql = "INSERT INTO #{table_name} VALUES ( $1, $2, $3, $4 )"
  db.transaction do |db_tr|
    rows.each do |line|
      db_tr.exec sql, line
    end
  end
end
new_connection() click to toggle source

Create a new table.

# File lib/poefy/pg.rb, line 137
def new_connection
  open_connection
end
open_connection() click to toggle source

Open a connection to the database.

# File lib/poefy/pg.rb, line 142
def open_connection
  @db ||= Database::connection
end
sproc_lines_by_rhyme(rhyme) click to toggle source

Find all lines for a certain rhyme.

# File lib/poefy/pg.rb, line 260
def sproc_lines_by_rhyme rhyme
  rs = db.exec_prepared 'la', [rhyme]
  rs.values.map do |row|
    {
      'line'       => row[0],
      'syllables'  => row[1].to_i,
      'final_word' => row[2],
      'rhyme'      => row[3]
    }
  end
end
sproc_lines_by_rhyme_syllables(rhyme, syllable_min_max) click to toggle source

Also adds syllable selection.

# File lib/poefy/pg.rb, line 273
def sproc_lines_by_rhyme_syllables rhyme, syllable_min_max
  arg_array = [
    rhyme,
    syllable_min_max[:min],
    syllable_min_max[:max]
  ]
  rs = db.exec_prepared 'las', arg_array
  rs.values.map do |row|
    {
      'line'       => row[0],
      'syllables'  => row[1].to_i,
      'final_word' => row[2],
      'rhyme'      => row[3]
    }
  end
end
sproc_rhymes_by_count(rhyme_count) click to toggle source

Find rhymes and counts greater than a certain length.

# File lib/poefy/pg.rb, line 233
def sproc_rhymes_by_count rhyme_count
  rs = db.exec_prepared 'rbc', [rhyme_count]
  rs.values.map do |row|
    {
      'rhyme' => row[0],
      'count' => row[1].to_i
    }
  end
end
sproc_rhymes_by_count_syllables(rhyme_count, syllable_min_max) click to toggle source

Also adds syllable selection.

# File lib/poefy/pg.rb, line 244
def sproc_rhymes_by_count_syllables rhyme_count, syllable_min_max
  arg_array = [
    syllable_min_max[:min],
    syllable_min_max[:max],
    rhyme_count
  ]
  rs = db.exec_prepared 'rbcs', arg_array
  rs.values.map do |row|
    {
      'rhyme' => row[0],
      'count' => row[1].to_i
    }
  end
end
sprocs_sql_hash() click to toggle source

Define SQL of the stored procedures.

# File lib/poefy/pg.rb, line 186
      def sprocs_sql_hash
        sql = {}
        sql[:rbc] = <<-SQL
          SELECT rhyme, COUNT(rhyme) AS count
          FROM (
            SELECT rhyme, final_word, COUNT(final_word) AS wc
            FROM #{table}
            GROUP BY rhyme, final_word
          ) AS sub_table
          GROUP BY rhyme
          HAVING COUNT(rhyme) >= $1
        SQL
        sql[:rbcs] = <<-SQL
          SELECT rhyme, COUNT(rhyme) AS count
          FROM (
            SELECT rhyme, final_word, COUNT(final_word) AS wc
            FROM #{table}
            WHERE syllables BETWEEN $1 AND $2
            GROUP BY rhyme, final_word
          ) AS sub_table
          GROUP BY rhyme
          HAVING COUNT(rhyme) >= $3
        SQL
        sql[:la] = <<-SQL
          SELECT line, syllables, final_word, rhyme
          FROM #{table} WHERE rhyme = $1
        SQL
        sql[:las] = <<-SQL
          SELECT line, syllables, final_word, rhyme
          FROM #{table} WHERE rhyme = $1
          AND syllables BETWEEN $2 AND $3
        SQL
        sql
      end
table() click to toggle source

The name of the table.

# File lib/poefy/pg.rb, line 132
def table
  @name
end