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
 
# 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, debug: debug    

  @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';"
      a = @db.execute(sql).flat_map(&:to_a)        
      sys = a.grep /^sqlite_/
      @table = (a - sys).first
      @primary_key = @db.table_info(@table).map {|x| x['name'].to_sym }.first
    end
          
  end
  
  @sql =  sql || 'select * from ' + @table.to_s
  @default_sql = @sql
  @pagesize = pagesize
  @a = nil
  
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



58
59
60
61
62
 
# File 'lib/recordx_sqlite.rb', line 58

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

#create(h = {}) ⇒ Object 



64
65
66
67
68
69
70
71
72
73
74
75
 
# File 'lib/recordx_sqlite.rb', line 64

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 



77
78
79
80
81
82
83
 
# File 'lib/recordx_sqlite.rb', line 77

def delete(id)
  
  sql = "DELETE FROM #{@table} WHERE #{@primary_key}='#{id}'"
  @db.execute sql
  
  :delete
end

#find(id) ⇒ Object 



85
86
87
88
89
90
 
# File 'lib/recordx_sqlite.rb', line 85

def find(id)

  query(@sql) unless @a
  @a.find {|x| x.method(@primary_key).call == id}
  
end

#first(n = 1) ⇒ Object

returns the 1st n rows



94
95
96
 
# File 'lib/recordx_sqlite.rb', line 94

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

#order(dir = :asc) ⇒ Object 



98
99
100
101
 
# File 'lib/recordx_sqlite.rb', line 98

def order(dir=:asc)
  @sql += " ORDER BY #{@primary_key} #{dir.to_s.upcase}"
  self
end

#page(n = 1) ⇒ Object 



103
104
105
106
107
108
 
# File 'lib/recordx_sqlite.rb', line 103

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 



110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
 
# File 'lib/recordx_sqlite.rb', line 110

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 



136
137
138
139
 
# File 'lib/recordx_sqlite.rb', line 136

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

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



141
142
143
144
145
146
147
148
149
150
151
152
153
 
# File 'lib/recordx_sqlite.rb', line 141

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