class Poefy::Database
Public Class Methods
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 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 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
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
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
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
Get/set the description of the database.
# File lib/poefy/sqlite3.rb, line 72 def desc Database::desc @name end
# File lib/poefy/sqlite3.rb, line 75 def desc=(description) execute! "DELETE FROM comment;" execute! "INSERT INTO comment VALUES ( ? );", description.to_s end
See if the database file exists or not.
# File lib/poefy/sqlite3.rb, line 88 def exists? File.exists?(db_file) end
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
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 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 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
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 a query.
# File lib/poefy/sqlite3.rb, line 134 def execute! sql, *args db.execute sql, *args end
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
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 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
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
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
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
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
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
The name of the table.
# File lib/poefy/sqlite3.rb, line 116 def table 'lines' end