class Poefy::Database

Public Class Methods

desc(database_name) click to toggle source

Get the description of a database.

# File lib/poefy/sqlite3.rb, line 38
def self.desc database_name
  begin
    sql = "SELECT comment FROM comment;"
    Database::single_exec!(database_name, sql).flatten.first
  rescue
    ''
  end
end
list() click to toggle source

List all database files in the directory. Does not include databases used for testing.

# File lib/poefy/sqlite3.rb, line 29
def self.list
  Dir[Poefy.root + '/data/*.db'].map do |i|
    File.basename(i, '.db')
  end.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/sqlite3.rb, line 48
def self.list_with_desc
  Database::list.map do |i|
    begin
      [i, Database::desc(i)]
    rescue
      [i, '']
    end
  end.to_h
end
path(database_name) click to toggle source

Get the path of a database.

# File lib/poefy/sqlite3.rb, line 59
def self.path database_name
  Poefy.root + '/data/' + File.basename(database_name, '.db') + '.db'
end
single_exec!(database_name, sql) click to toggle source

Open a connection, execute a query, close the connection.

# File lib/poefy/sqlite3.rb, line 19
def self.single_exec! database_name, sql
  path = Database::path database_name
  con = SQLite3::Database.open path
  rs = con.execute sql
  con.close
  rs
end

Public Instance Methods

count() click to toggle source

The number of lines in the table.

# File lib/poefy/sqlite3.rb, line 81
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 database.

# File lib/poefy/sqlite3.rb, line 72
def desc
  Database::desc @name
end
desc=(description) click to toggle source
# File lib/poefy/sqlite3.rb, line 75
def desc=(description)
  execute! "DELETE FROM comment;"
  execute! "INSERT INTO comment VALUES ( ? );", description.to_s
end
exists?() click to toggle source

See if the database file exists or not.

# File lib/poefy/sqlite3.rb, line 88
def exists?
  File.exists?(db_file)
end
rhymes(word, key = nil) click to toggle source

Get all rhyming lines for the word.

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

      sql = <<-SQL
        SELECT rhyme, final_word, syllables, line
        FROM lines
        WHERE rhyme = ?
        ORDER BY rhyme, final_word, syllables, line
      SQL
      output = word.to_phrase.rhymes.keys.map do |rhyme|
        rs = execute!(sql, [rhyme]).to_a
        rs.each{ |a| a.reject!{ |k| k.is_a? Numeric }}
      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/sqlite3.rb, line 67
def type
  'sqlite3'
end

Private Instance Methods

create_sprocs() click to toggle source

Create the stored procedures in the database.

# File lib/poefy/sqlite3.rb, line 229
def create_sprocs
  sprocs_sql_hash.each do |key, value|
    @sproc[key] = db.prepare 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/sqlite3.rb, line 168
      def create_table table_name, description = nil
        execute! <<-SQL
          CREATE TABLE #{table_name} (
            line        TEXT,
            syllables   SMALLINT,
            final_word  TEXT,
            rhyme       TEXT
          );
        SQL
        execute! <<-SQL
          CREATE TABLE comment (
            comment     TEXT
          );
        SQL
        execute! <<-SQL
          CREATE INDEX idx ON #{table_name} (
            rhyme, final_word, line
          );
        SQL
        self.desc = description
      end
db_file() click to toggle source

Find the correct database file. If local, just use the value. Else, use the database in /data/ directory.

# File lib/poefy/sqlite3.rb, line 153
def db_file
  if @local
    @name
  elsif @db_file
    @db_file
  else
    path = Poefy.root + '/data'
    file = File.basename(@name, '.db')
    @db_file = path + '/' + file + '.db'
  end
end
execute!(sql, *args) click to toggle source

Execute a query.

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

Insert an array of poefy-described lines.

# File lib/poefy/sqlite3.rb, line 139
def insert_lines table_name, rows
  sql = "INSERT INTO #{table_name} VALUES ( ?, ?, ?, ? )"
  db.transaction do |db_tr|
    rows.each do |line|
      db_tr.execute sql, line
    end
  end
end
new_connection() click to toggle source

Create a new database.

# File lib/poefy/sqlite3.rb, line 121
def new_connection
  File.delete(db_file) if File.exists?(db_file)
  @db = SQLite3::Database.new(db_file)
  @db.results_as_hash = true
end
open_connection() click to toggle source

Open a connection to the database.

# File lib/poefy/sqlite3.rb, line 128
def open_connection
  @db = SQLite3::Database.open(db_file)
  @db.results_as_hash = true
end
sproc_lines_by_rhyme(rhyme) click to toggle source

Find all lines for a certain rhyme.

# File lib/poefy/sqlite3.rb, line 256
def sproc_lines_by_rhyme rhyme
  @sproc[:la].reset!
  @sproc[:la].bind_param(1, rhyme)
  @sproc[:la].execute.to_a
end
sproc_lines_by_rhyme_syllables(rhyme, syllable_min_max) click to toggle source

Also adds syllable selection.

# File lib/poefy/sqlite3.rb, line 263
def sproc_lines_by_rhyme_syllables rhyme, syllable_min_max
  @sproc[:las].reset!
  @sproc[:las].bind_param(1, rhyme)
  @sproc[:las].bind_param(2, syllable_min_max[:min])
  @sproc[:las].bind_param(3, syllable_min_max[:max])
  @sproc[:las].execute.to_a
end
sproc_rhymes_by_count(rhyme_count) click to toggle source

Find rhymes and counts greater than a certain length.

# File lib/poefy/sqlite3.rb, line 240
def sproc_rhymes_by_count rhyme_count
  @sproc[:rbc].reset!
  @sproc[:rbc].bind_param(1, rhyme_count)
  @sproc[:rbc].execute.to_a
end
sproc_rhymes_by_count_syllables(rhyme_count, syllable_min_max) click to toggle source

Also adds syllable selection.

# File lib/poefy/sqlite3.rb, line 247
def sproc_rhymes_by_count_syllables rhyme_count, syllable_min_max
  @sproc[:rbcs].reset!
  @sproc[:rbcs].bind_param(1, syllable_min_max[:min])
  @sproc[:rbcs].bind_param(2, syllable_min_max[:max])
  @sproc[:rbcs].bind_param(3, rhyme_count)
  @sproc[:rbcs].execute.to_a
end
sprocs_sql_hash() click to toggle source

Define SQL of the stored procedures.

# File lib/poefy/sqlite3.rb, line 193
      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
          )
          GROUP BY rhyme
          HAVING count >= ?
        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 ? AND ?
            GROUP BY rhyme, final_word
          )
          GROUP BY rhyme
          HAVING count >= ?
        SQL
        sql[:la] = <<-SQL
          SELECT line, syllables, final_word, rhyme
          FROM #{table} WHERE rhyme = ?
        SQL
        sql[:las] = <<-SQL
          SELECT line, syllables, final_word, rhyme
          FROM #{table} WHERE rhyme = ?
          AND syllables BETWEEN ? AND ?
        SQL
        sql
      end
table() click to toggle source

The name of the table.

# File lib/poefy/sqlite3.rb, line 116
def table
  'lines'
end