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

def initialize(dbfile, table: '', primary_key: :id, pk: primary_key, 
               sql: nil, pagesize: 10)
      
  @db = SQLite3::Database.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?
          
  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



42
43
44
45
46
 
# File 'lib/recordx_sqlite.rb', line 42

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

#create(h = {}) ⇒ Object 



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

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 



61
62
63
64
65
66
67
 
# File 'lib/recordx_sqlite.rb', line 61

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

#find(id) ⇒ Object 



69
70
71
72
73
74
 
# File 'lib/recordx_sqlite.rb', line 69

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



78
79
80
 
# File 'lib/recordx_sqlite.rb', line 78

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

#order(dir = :asc) ⇒ Object 



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

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

#page(n = 1) ⇒ Object 



87
88
89
90
91
92
 
# File 'lib/recordx_sqlite.rb', line 87

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 



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

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 



109
110
111
112
 
# File 'lib/recordx_sqlite.rb', line 109

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

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



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

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