class RecordxSqlite
Public Class Methods
new(dbfile, table: nil, primary_key: :id, pk: primary_key, sql: nil, pagesize: 10, debug: false)
click to toggle source
# File lib/recordx_sqlite.rb, line 12 def initialize(dbfile, table: nil, primary_key: :id, pk: primary_key, sql: nil, pagesize: 10, debug: false) @debug = debug sqlite = dbfile =~ /^sqlite:\/\// ? DRbSQLite : SQLite3::Database @db = sqlite.new dbfile @db.results_as_hash = true if table.is_a? String then @table, @primary_key = table, pk.to_sym elsif table.is_a? Hash h = table @table = h.keys.first @primary_key = h[@table].keys.first create_table(@table, h[@table]) if @db.table_info(@table).empty? else if @db.respond_to? :tables then @table = @db.tables.first @primary_key = @db.fields(@table).first else sql = "SELECT name FROM sqlite_master WHERE type='table';" r = @db.execute(sql) puts 'r: ' + r.inspect if @debug a = r.map {|x| x['name']} puts 'a: ' + a.inspect if @debug sys = a.grep /^sqlite_/ @table = (a - sys).first puts '@table: ' + @table.inspect if @debug @primary_key = @db.table_info(@table).map {|x| x['name'].to_sym }.first puts '@primary_key: ' + @primary_key.inspect if @debug end end @sql = sql = 'select * from ' + @table.to_s @default_sql = @sql @pagesize = pagesize @a = nil # create the fhe find_by methods for each fields @db.table_info(@table).each do |cols| #puts 'cols: ' + cols.inspect define_singleton_method ('find_by_' + cols['name']).to_sym do |val| find_by(cols['name'], val) end define_singleton_method ('find_all_by_' + cols['name']).to_sym do |val| find_all_by(cols['name'], val) end end end
Public Instance Methods
all()
click to toggle source
note: when using method all() you will need to call method refresh() first if a record had recently been added since the recordset was loaded
# File lib/recordx_sqlite.rb, line 80 def all() @sql = @default_sql query(@sql) unless @a @a end
create(h={})
click to toggle source
# File lib/recordx_sqlite.rb, line 86 def create(h={}) fields = h.keys values = h.values sql = "INSERT INTO #{@table} (#{fields.join(', ')}) VALUES (#{(['?'] * fields.length).join(', ')})" @db.execute(sql, values) :create end
delete(id)
click to toggle source
# File lib/recordx_sqlite.rb, line 99 def delete(id) sql = "DELETE FROM #{@table} WHERE #{@primary_key}='#{id}'" @db.execute sql :delete end
find(id)
click to toggle source
# File lib/recordx_sqlite.rb, line 107 def find(id) query(@sql) unless @a @a.find {|x| x.method(@primary_key).call == id} end
find_all_by(field, val)
click to toggle source
# File lib/recordx_sqlite.rb, line 121 def find_all_by(field, val) query(@sql) unless @a @a.select {|x| x.method(field).call.downcase == val.downcase} end
find_by(field, val)
click to toggle source
# File lib/recordx_sqlite.rb, line 114 def find_by(field, val) query(@sql) unless @a @a.find {|x| x.method(field).call == val} end
first(n=1)
click to toggle source
returns the 1st n rows
# File lib/recordx_sqlite.rb, line 130 def first(n=1) query(@sql + ' LIMIT ' + n.to_s, cache: false) end
order(dir=:asc)
click to toggle source
# File lib/recordx_sqlite.rb, line 134 def order(dir=:asc) @sql += " ORDER BY #{@primary_key} #{dir.to_s.upcase}" self end
page(n=1)
click to toggle source
# File lib/recordx_sqlite.rb, line 139 def page(n=1) query(@sql + " ORDER BY %s DESC LIMIT %s OFFSET %s" % [@primary_key, @pagesize, @pagesize*(n-1)], cache: false) end
query(sql=@sql, cache: true, heading: true)
click to toggle source
# File lib/recordx_sqlite.rb, line 146 def query(sql=@sql, cache: true, heading: true) if heading then rs = @db.query sql else @db.results_as_hash = false rs = @db.query(sql).to_a @db.results_as_hash = true return rs end a = rs.map do |h| h2 = h.inject({}) {|r, x| k, v = x; r.merge(k.to_sym => v)} RecordX.new(h2, self, h2[@primary_key]) end @a = a if cache return a end
refresh()
click to toggle source
# File lib/recordx_sqlite.rb, line 172 def refresh() query(@sql) 'refreshed' end
update(id, h={})
click to toggle source
# File lib/recordx_sqlite.rb, line 177 def update(id, h={}) col, value = h.to_a.first return if col == @primary_key s = " UPDATE #{@table} SET #{col}='#{value}' WHERE #{@primary_key.to_s}='#{id}';" @db.execute(s) end
Private Instance Methods
create_table(name, cols)
click to toggle source
# File lib/recordx_sqlite.rb, line 193 def create_table(name, cols) fields = cols.map do |k,v| types = { string: :text, integer: :int, float: :real, date: :date } type = types[v.class.to_s.downcase.to_sym].to_s.upcase "%s %s" % [k.to_s, type] end sql = "CREATE TABLE %s (\n %s PRIMARY KEY NOT NULL,\n %s\n);" % [name, fields.first, fields[1..-1].join(",\n ")] @db.execute sql end