Class: RecordxSqlite

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

Instance Method Summary collapse

Constructor Details

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

Returns a new instance of RecordxSqlite.



11
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
 
# File 'lib/recordx_sqlite.rb', line 11

def initialize(dbfile, table: '', primary_key: :id, pk: primary_key, 
               sql: nil, pagesize: 10, debug: true)

  @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?
          
  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



44
45
46
47
48
 
# File 'lib/recordx_sqlite.rb', line 44

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

#create(h = {}) ⇒ Object 



50
51
52
53
54
55
56
57
58
59
60
61
 
# File 'lib/recordx_sqlite.rb', line 50

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 



63
64
65
66
67
68
69
 
# File 'lib/recordx_sqlite.rb', line 63

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

#find(id) ⇒ Object 



71
72
73
74
75
76
 
# File 'lib/recordx_sqlite.rb', line 71

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



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

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

#order(dir = :asc) ⇒ Object 



84
85
86
87
 
# File 'lib/recordx_sqlite.rb', line 84

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

#page(n = 1) ⇒ Object 



89
90
91
92
93
94
 
# File 'lib/recordx_sqlite.rb', line 89

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) ⇒ Object 



96
97
98
99
100
101
102
103
104
105
106
107
108
109
 
# File 'lib/recordx_sqlite.rb', line 96

def query(sql=@sql, cache: true)
  
  rs = @db.query sql
  
  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 



111
112
113
114
 
# File 'lib/recordx_sqlite.rb', line 111

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

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



116
117
118
119
120
121
122
123
124
125
126
127
128
 
# File 'lib/recordx_sqlite.rb', line 116

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