Module: Waxx::Sqlite3

Extended by:
Object, Sqlite3
Included in:
Sqlite3
Defined in:
waxx/sqlite3.rb

Overview

The SQLite Object methods

Instance Attribute Summary collapse

Instance Method Summary collapse

Methods included from Object

debug

Instance Attribute Details

#app ⇒ Object (readonly)

Returns the value of attribute app



10
11
12
 
# File 'waxx/sqlite3.rb', line 10

def app
  @app
end

#columns ⇒ Object (readonly)

Returns the value of attribute columns



13
14
15
 
# File 'waxx/sqlite3.rb', line 13

def columns
  @columns
end

#db ⇒ Object (readonly)

Returns the value of attribute db



11
12
13
 
# File 'waxx/sqlite3.rb', line 11

def db
  @db
end

#joins ⇒ Object (readonly)

Returns the value of attribute joins



15
16
17
 
# File 'waxx/sqlite3.rb', line 15

def joins
  @joins
end

#orders ⇒ Object (readonly)

Returns the value of attribute orders



16
17
18
 
# File 'waxx/sqlite3.rb', line 16

def orders
  @orders
end

#pkey ⇒ Object (readonly)

Returns the value of attribute pkey



14
15
16
 
# File 'waxx/sqlite3.rb', line 14

def pkey
  @pkey
end

#table ⇒ Object (readonly)

Returns the value of attribute table



12
13
14
 
# File 'waxx/sqlite3.rb', line 12

def table
  @table
end

Instance Method Details

#/(n) ⇒ Object 



71
72
73
 
# File 'waxx/sqlite3.rb', line 71

def /(n)
  @columns[n.to_sym]
end

#[](n) ⇒ Object 



67
68
69
 
# File 'waxx/sqlite3.rb', line 67

def [](n)
  @columns[n.to_sym]
end

#cast(col, val) ⇒ Object 



217
218
219
220
221
222
223
224
225
226
 
# File 'waxx/sqlite3.rb', line 217

def cast(col, val)
  case col[:type].to_sym
  when :int
    val.to_s.empty? ? nil : val.to_i
  when :float, :numeric
    val.to_s.empty? ? nil : val.to_f
  else
    val
  end
end

#connect(str) ⇒ Object

Connect to an sqlite database

Set in config.yaml:

databases:
  blog: sqlite:///path/to/database.db

Or call this with the path to the db file



25
26
27
28
29
 
# File 'waxx/sqlite3.rb', line 25

def connect(str)
  conn = SQLite3::Database.new( str )
  conn.results_as_hash = true
  conn
end

#delete(x, id) ⇒ Object 



228
229
230
 
# File 'waxx/sqlite3.rb', line 228

def delete(x, id)
  x.db[@db].execute("DELETE FROM #{@table} WHERE #{@pkey} = ?", [id])
end

#get(x, select: nil, id: nil, joins: nil, where: nil, having: nil, order: nil, limit: nil, offset: nil, view: nil) {|q| ... } ⇒ Object

Yields:

  • (q) 


106
107
108
109
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
135
136
137
138
139
140
141
 
# File 'waxx/sqlite3.rb', line 106

def get(x, select:nil, id:nil, joins:nil,  where:nil, having:nil, order:nil, limit:nil, offset:nil, view:nil, &blk)
  Waxx.debug "object.get"
  select = parse_select(select, view)
  where = ["#{@table}.#{@pkey} = ?",id] if id and where.nil?
  # Block SQL injection in order clause. All order options must be defined in @orders.
  if order
    if not @orders/order
      Waxx.debug("ERROR: Object.get order (#{order}) not found in @orders [#{@orders.keys.join(", ")}]. Sorting by #{@pkey} instead.")
      order = @pkey
    else
      order = @orders/order 
    end
  end
  if joins.nil? and view
    joins = view.joins_to_sql
  end
  q = {select:select, joins:joins, where:where, having:having, order:order, limit:limit, offset:offset}
  yield q if block_given?
  Waxx.debug "object.get.select: #{q[:select]}"
  return [] if q[:select].empty?
  sql = []
  sql << "SELECT #{q[:select] || "*"}"
  sql << "FROM #{@table} #{q[:joins]}"
  sql << "WHERE #{q[:where][0]}" if q[:where] 
  sql << "HAVING #{q[:having[0]]}" if q[:having] 
  sql << "ORDER BY #{q[:order]}" if q[:order]
  sql << "LIMIT #{q[:limit].to_i}" if q[:limit]
  sql << "OFFSET #{q[:offset].to_i}" if q[:offset]
  vals = []
  vals << q[:where][1] if q[:where] and q[:where][1]
  vals << q[:having][1] if q[:having] and q[:having][1]
  #[sql.join(" "), vals.flatten]
  Waxx.debug sql
  Waxx.debug vals.join(", ")
  x.db[@db].execute(sql.join(" "), vals.flatten)
end

#get_by_id(x, id, select = nil, view: nil) ⇒ Object Also known as: by_id 



143
144
145
 
# File 'waxx/sqlite3.rb', line 143

def get_by_id(x, id, select=nil, view:nil)
  get(x, id: id, select: select, view: view).first
end

#get_cols(*args) ⇒ Object 



75
76
77
78
79
 
# File 'waxx/sqlite3.rb', line 75

def get_cols(*args)
  re = {}
  args.flatten.map{|a| re[a] = @columns[a.to_sym]}
  re
end

#has(opts = nil) ⇒ Object 



42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
 
# File 'waxx/sqlite3.rb', line 42

def has(opts=nil)
  init if @table.nil?
  return @columns if opts.nil?
  @columns = opts
  @columns.each{|n,v|
    v[:table] = @table
    v[:column] = n
    v[:views] = []
    v[:label] ||= Waxx::Util.label(n)
    @orders[n] = v[:order] || n
    @orders["_#{n}".to_sym] = v[:_order] || "#{n} DESC"
    @pkey = n if v[:pkey]
    if v[:is]
      r, tc = v[:is].split(":")
      t, c = tc.split(".")
      @joins[r] = {join: "INNER", table: t, col: c}
    end
    if v[:has]
      r, tc = v[:has].split(":")
      t, c = tc.split(".")
      @joins[r] = {join: "LEFT", table: t, col: c}
    end
  }
end

#init(app: nil, db: "app", table: nil, pk: "id", cols: nil) ⇒ Object 



31
32
33
34
35
36
37
38
39
40
 
# File 'waxx/sqlite3.rb', line 31

def init(app:nil, db:"app", table:nil, pk:"id", cols:nil)
  @app ||= (app || App.table_from_class(name)).to_sym
  @db ||= db.to_sym
  @table ||= (table || App.table_from_class(name)).to_sym
  @pkey ||= pk.to_sym
  @columns ||= {}
  @joins ||= {}
  @orders ||= {}
  has(cols) if cols
end

#order(req_order, default_order = '') ⇒ Object 



232
233
234
235
236
 
# File 'waxx/sqlite3.rb', line 232

def order(req_order, default_order='')
  return default_order if req_order.nil?
  return orders[req_order.to_sym] if orders.has_key? req_order.to_sym
  @pkey
end

#parse_select(select, view) ⇒ Object 



91
92
93
94
95
96
97
98
99
100
101
102
103
104
 
# File 'waxx/sqlite3.rb', line 91

def parse_select(select, view)
  raise "Can not define both select and view in Waxx::Object.parse_select (#{name})." if select and view
  return select || "*" if view.nil?
  view.columns.map{|n,c|             
    raise "Column #{n} not defined in #{view}" if c.nil?
    if c[:sql_select]
       "#{c[:sql_select]} AS #{n}"
    elsif n != c[:column]
      "#{c[:table]}.#{c[:column]} AS #{n}"
    else 
      "#{c[:table]}.#{c[:column]}"
    end
  }.join(", ")
end

#post(x, data, cols: nil, returning: nil, view: nil, &blk) ⇒ Object 



148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
 
# File 'waxx/sqlite3.rb', line 148

def post(x, data, cols:nil, returning:nil, view:nil, &blk)
  if view
    cols = view.columns.select{|n,c| c[:table] == @table}
  else
    cols ||= @columns
  end
  data = blk.call if block_given?
  sql = "INSERT INTO #{@table} ("
  names = []
  vars = []
  vals = []
  ret = []
  i = 1
  cols.each{|n,v|
    if data/n
      names << n.to_s
      vars << "?"
      vals << cast(v, data/n)
      i += 1
    end
    ret << n.to_s
  }
  sql << names.join(",")
  sql << ") VALUES (#{vars.join(",")})"
  Waxx.debug(sql, 4)
  Waxx.debug(vals, 4)
  x.db[@db].execute(sql, vals) 
  new_id = x.db[@db].last_insert_row_id
  x.db[@db].execute("SELECT #{returning || ret.join(",")} FROM #{@table} WHERE #{@pkey} = ?", [new_id]).first
end

#put(x, id, data, cols: nil, returning: nil, view: nil, where: nil, &blk) ⇒ Object Also known as: patch 



179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
 
# File 'waxx/sqlite3.rb', line 179

def put(x, id, data, cols:nil, returning:nil, view:nil, where:nil, &blk)
  if view
    cols = view.columns.select{|n,c| c[:table] == @table}
  else
    cols ||= @columns
  end
  data = blk.call if block_given?
  sql = "UPDATE #{@table} SET "
  set = []
  vals = []
  ret = []
  i = 1
  Waxx.debug "data: #{data}"
  cols.each{|n,v|
    Waxx.debug "col: #{n}: #{v.inspect}"
    if data.has_key? n.to_s or data.has_key? n.to_sym
      set << "#{n} = ?"
      vals << cast(v, data/n)
      ret << n.to_s
      i += 1
    end
  }
  sql << set.join(",")
  sql << " WHERE #{@pkey} = ? #{where} RETURNING #{returning || ret.join(",")}"
  vals << id
  Waxx.debug(sql)
  Waxx.debug(vals)
  x.db[@db].execute(sql, vals).first 
end

#put_post(x, id, data, cols: nil, returning: nil, view: nil) ⇒ Object 



210
211
212
213
214
215
 
# File 'waxx/sqlite3.rb', line 210

def put_post(x, id, data, cols:nil, returning:nil, view: nil)
  q = nil
  q = get_by_id(x, id, @pkey) if id.to_i > 0
  return post(x, data, cols: cols, returning: returning, view: view) if q.nil?
  put(x, id, data, cols: cols, returning: returning, view: view)
end

#run(x, act, meth, *args) ⇒ Object 



87
88
89
 
# File 'waxx/sqlite3.rb', line 87

def run(x, act, meth, *args)
  App[@app][act.to_sym][meth.to_sym][x, *args]
end

#runs(opts = nil) ⇒ Object 



81
82
83
84
85
 
# File 'waxx/sqlite3.rb', line 81

def runs(opts=nil)
  init if @app.nil?
  return App[@app] if opts.nil?
  App[@app] = opts
end