Class: RecordxSqlite

Inherits:
Object
  • Object
show all
Defined in:
lib/recordx_sqlite.rb

Instance Method Summary collapse

Constructor Details

#initialize(dbfile, table: nil, primary_key: :id, pk: primary_key, sql: nil, pagesize: 10, debug: false) ⇒ RecordxSqlite

Returns a new instance of RecordxSqlite.



12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
 
# 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

Instance Method Details

#all ⇒ Object

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



80
81
82
83
84
 
# File 'lib/recordx_sqlite.rb', line 80

def all()    
  @sql = @default_sql
  query(@sql) unless @a
  @a
end

#create(h = {}) ⇒ Object 



86
87
88
89
90
91
92
93
94
95
96
97
 
# 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) ⇒ Object 



99
100
101
102
103
104
105
 
# 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) ⇒ Object 



107
108
109
110
111
112
 
# 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) ⇒ Object 



121
122
123
124
125
126
 
# 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) ⇒ Object 



114
115
116
117
118
119
 
# 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) ⇒ Object

returns the 1st n rows



130
131
132
 
# File 'lib/recordx_sqlite.rb', line 130

def first(n=1)
 query(@sql + ' LIMIT ' + n.to_s, cache: false)
end

#order(dir = :asc) ⇒ Object 



134
135
136
137
 
# 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) ⇒ Object 



139
140
141
142
143
144
 
# 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) ⇒ Object 



146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
 
# 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 ⇒ Object 



172
173
174
175
 
# File 'lib/recordx_sqlite.rb', line 172

def refresh()
  query(@sql)
  'refreshed'
end

#update(id, h = {}) ⇒ Object 



177
178
179
180
181
182
183
184
185
186
187
188
189
 
# 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